dhis2-devs team mailing list archive
-
dhis2-devs team
-
Mailing list archive
-
Message #23379
[Branch ~dhis2-devs-core/dhis2/trunk] Rev 11375: Improve aggregate query builder performance (WIP).
------------------------------------------------------------
revno: 11375
committer: Tran Chau <tran.hispvietnam@xxxxxxxxx>
branch nick: dhis2
timestamp: Wed 2013-07-10 12:10:43 +0700
message:
Improve aggregate query builder performance (WIP).
modified:
dhis-2/dhis-services/dhis-service-patient/src/main/java/org/hisp/dhis/caseaggregation/jdbc/JdbcCaseAggregationConditionManager.java
--
lp:dhis2
https://code.launchpad.net/~dhis2-devs-core/dhis2/trunk
Your team DHIS 2 developers is subscribed to branch lp:dhis2.
To unsubscribe from this branch go to https://code.launchpad.net/~dhis2-devs-core/dhis2/trunk/+edit-subscription
=== modified file 'dhis-2/dhis-services/dhis-service-patient/src/main/java/org/hisp/dhis/caseaggregation/jdbc/JdbcCaseAggregationConditionManager.java'
--- dhis-2/dhis-services/dhis-service-patient/src/main/java/org/hisp/dhis/caseaggregation/jdbc/JdbcCaseAggregationConditionManager.java 2013-07-05 07:05:20 +0000
+++ dhis-2/dhis-services/dhis-service-patient/src/main/java/org/hisp/dhis/caseaggregation/jdbc/JdbcCaseAggregationConditionManager.java 2013-07-10 05:10:43 +0000
@@ -789,14 +789,14 @@
{
String keyExist = (isExist == true) ? "EXISTS" : "NOT EXISTS";
- String sql = " " + keyExist + " ( SELECT * "
+ String sql = " " + keyExist + " ( SELECT _psi.programstageinstanceid "
+ "FROM patientdatavalue _pdv inner join programstageinstance _psi "
+ "ON _pdv.programstageinstanceid=_psi.programstageinstanceid JOIN programinstance _pi "
+ "ON _pi.programinstanceid=_psi.programinstanceid "
+ "WHERE psi.programstageinstanceid=_pdv.programstageinstanceid AND _pdv.dataelementid=" + dataElementId
+ " AND _psi.organisationunitid in (" + TextUtils.getCommaDelimitedString( orgunitIds ) + ") "
- + "AND _pi.programid = " + programId + " AND _psi.executionDate>='" + startDate
- + "' AND _psi.executionDate <= '" + endDate + "' ";
+ + "AND _pi.programid = " + programId + " AND ( _psi.executionDate BETWEEN '" + startDate + "' AND '"
+ + endDate + "') ";
if ( !programStageId.equals( IN_CONDITION_GET_ALL ) )
{
@@ -825,8 +825,8 @@
*/
private String getConditionForPatientAttribute( int attributeId, Collection<Integer> orgunitIds )
{
- String sql = " EXISTS ( SELECT * " + "FROM patientattributevalue _pav "
- + "WHERE _pav.patientid = pi.patientid " + "and _pav.patientattributeid=" + attributeId
+ String sql = " EXISTS ( SELECT _pav.patientid FROM patientattributevalue _pav "
+ + "WHERE _pav.patientid = pi.patientid AND _pav.patientattributeid=" + attributeId
+ " AND p.organisationunitid in (" + TextUtils.getCommaDelimitedString( orgunitIds ) + ") AND _pav.value ";
return sql;
@@ -840,8 +840,8 @@
private String getConditionForPatient( Collection<Integer> orgunitIds, String operator, String startDate,
String endDate )
{
- String sql = " EXISTS ( SELECT * " + "FROM patient _p " + "WHERE _p.patientid = pi.patientid "
- + "AND _p.registrationdate>='" + startDate + "' AND _p.registrationdate<='" + endDate + "' "
+ String sql = " EXISTS ( SELECT _p.patientid FROM patient _p " + "WHERE _p.patientid = pi.patientid "
+ + "AND ( _p.registrationdate BETWEEN '" + startDate + "' AND '" + endDate + "') "
+ "AND p.organisationunitid in (" + TextUtils.getCommaDelimitedString( orgunitIds ) + ") ";
return sql;
@@ -855,7 +855,7 @@
private String getConditionForPatientProperty( String propertyName, String operator, String startDate,
String endDate )
{
- String sql = " EXISTS (SELECT * FROM patient _p WHERE _p.patientid = pi.patientid AND ";
+ String sql = " EXISTS (SELECT _p.patientid FROM patient _p WHERE _p.patientid = pi.patientid AND ";
if ( propertyName.equals( PROPERTY_AGE ) )
{
@@ -877,9 +877,9 @@
private String getConditionForPatientProgramStageProperty( String propertyName, String operator, String startDate,
String endDate )
{
- String sql = " EXISTS ( SELECT * from programstageinstance _psi "
- + "WHERE _psi.programstageinstanceid=psi.programstageinstanceid AND _psi.executionDate>='" + startDate
- + "' and _psi.executionDate<='" + endDate + "' and " + propertyName;
+ String sql = " EXISTS ( SELECT _psi.programstageinstanceid from programstageinstance _psi "
+ + "WHERE _psi.programstageinstanceid=psi.programstageinstanceid AND ( _psi.executionDate BETWEEN '"
+ + startDate + "' AND '" + endDate + "') AND " + propertyName;
return sql;
}
@@ -892,15 +892,8 @@
*/
private String getConditionForProgramProperty( String operator, String startDate, String endDate, String property )
{
- String sql = " EXISTS ( SELECT * FROM programinstance as _pi WHERE psi.programinstanceid=_pi.programsinstanceid AND "
- + "_pi.enrollmentdate>='"
- + startDate
- + "' "
- + "AND _pi.enrollmentdate<='"
- + endDate
- + "' AND "
- + property
- + " ";
+ String sql = " EXISTS ( SELECT _pi.programinstanceid FROM programinstance as _pi WHERE psi.programinstanceid=_pi.programsinstanceid AND "
+ + "( psi.executionDate BETWEEN '" + startDate + "' AND '" + endDate + "') AND " + property + " ";
return sql;
}
@@ -913,11 +906,11 @@
private String getConditionForProgram( String programId, String operator, Collection<Integer> orgunitIds,
String startDate, String endDate )
{
- String sql = " EXISTS ( SELECT * FROM programinstance as _pi "
- + "inner join programstageinstance _psi on _pi.programinstanceid=_psi.programinstanceid "
+ String sql = " EXISTS ( SELECT _psi.programstageinstanceid FROM programinstance as _pi "
+ + "INNER JOIN programstageinstance _psi ON _pi.programinstanceid=_psi.programinstanceid "
+ "WHERE psi.programstageinstanceid=_psi.programstageinstanceid AND _pi.programid=" + programId + " "
+ " AND _psi.organisationunitid in (" + TextUtils.getCommaDelimitedString( orgunitIds )
- + ") AND _pi.enrollmentdate >= '" + startDate + "' AND _pi.enrollmentdate <= '" + endDate + "' ";
+ + ") AND (_psi.executionDate BETWEEN '" + startDate + "' AND '" + endDate + "') ";
return sql;
}
@@ -930,10 +923,10 @@
private String getConditionForProgramStage( String programStageId, String operator, Collection<Integer> orgunitIds,
String startDate, String endDate )
{
- String sql = " EXISTS ( SELECT * FROM programinstance as _pi INNER JOIN programstageinstance _psi "
+ String sql = " EXISTS ( SELECT _psi.programstageinstanceid FROM programinstance as _pi INNER JOIN programstageinstance _psi "
+ "ON _pi.programinstanceid = _psi.programinstanceid WHERE _psi.programstageinstanceid=psi.programstageinstanceid "
- + "AND _psi.programstageid=" + programStageId + " AND _psi.executiondate >= '" + startDate
- + "' AND _psi.executiondate <= '" + endDate + "' AND _psi.organisationunitid in ("
+ + "AND _psi.programstageid=" + programStageId + " AND (_psi.executionDate BETWEEN '" + startDate
+ + "' AND '" + endDate + "') AND _psi.organisationunitid in ("
+ TextUtils.getCommaDelimitedString( orgunitIds ) + ") ";
return sql;
@@ -948,10 +941,10 @@
private String getConditionForCountProgramStage( String programStageId, String operator,
Collection<Integer> orgunitIds, String startDate, String endDate )
{
- String sql = " EXISTS ( SELECT * FROM programstageinstance as _psi "
+ String sql = " EXISTS ( SELECT _psi.programstageinstanceid FROM programstageinstance as _psi "
+ "WHERE psi.programstageinstanceid=_psi.programstageinstanceid AND _psi.organisationunitid in ("
+ TextUtils.getCommaDelimitedString( orgunitIds ) + ") and _psi.programstageid = " + programStageId + " "
- + "AND _psi.executionDate >= '" + startDate + "' AND _psi.executionDate <= '" + endDate + "' "
+ + "AND ( _psi.executionDate BETWEEN '" + startDate + "' AND '" + endDate + "') "
+ "GROUP BY _psi.programinstanceid,_psi.programstageinstanceid "
+ "HAVING count(_psi.programstageinstanceid) ";
@@ -967,9 +960,9 @@
private String getConditionForProgramStageProperty( String property, String operator,
Collection<Integer> orgunitIds, String startDate, String endDate )
{
- String sql = " EXISTS ( SELECT * FROM programstageinstance _psi "
- + "WHERE psi.programstageinstanceid=_psi.programstageinstanceid AND _psi.executiondate >= '" + startDate
- + "' AND _psi.executiondate <= '" + endDate + "' AND _psi.organisationunitid in ("
+ String sql = " EXISTS ( SELECT _psi.programstageinstanceid FROM programstageinstance _psi "
+ + "WHERE psi.programstageinstanceid=_psi.programstageinstanceid AND (_psi.executionDate BETWEEN '"
+ + startDate + "' AND '" + endDate + "') AND _psi.organisationunitid in ("
+ TextUtils.getCommaDelimitedString( orgunitIds ) + ") AND " + property + " ";
return sql;
@@ -994,8 +987,7 @@
sql += " AND EXISTS ( SELECT programstageinstanceid FROM programstageinstance _psi "
+ " WHERE _psi.organisationunitid=ou.organisationunitid AND _psi.programstageid = " + programStageId
- + " AND _psi.completed=true AND _psi.executiondate >= '" + startDate + "' AND _psi.executiondate <= '"
- + endDate + "' ) ";
+ + " AND _psi.completed=true AND ( _psi.executionDate BETWEEN '" + startDate + "' AND '" + endDate + "') ) ";
return sql;
}
@@ -1003,14 +995,14 @@
private String getConditionForMinusDataElement( Collection<Integer> orgunitIds, Integer programStageId,
Integer dataElementId, String compareSide, String startDate, String endDate )
{
- return " EXISTS ( SELECT * FROM patientdatavalue _pdv inner join programstageinstance _psi "
+ return " EXISTS ( SELECT _psi.programstageinstanceid FROM patientdatavalue _pdv inner join programstageinstance _psi "
+ " ON _pdv.programstageinstanceid=_psi.programstageinstanceid "
+ " JOIN programinstance _pi ON _pi.programinstanceid=_psi.programinstanceid "
+ " WHERE psi.programstageinstanceid=_pdv.programstageinstanceid "
+ " AND _pdv.dataelementid=" + dataElementId
+ " AND _psi.organisationunitid in (" + TextUtils.getCommaDelimitedString( orgunitIds )
- + ") " + " AND _psi.programstageid = " + programStageId + " AND _psi.executionDate>='"
- + startDate + "' AND _psi.executionDate <= '" + endDate + "' "
+ + ") " + " AND _psi.programstageid = " + programStageId
+ + " AND ( _psi.executionDate BETWEEN '" + startDate + "' AND '" + endDate + "') "
+ " AND ( DATE(_pdv.value) - DATE(" + compareSide + ") ) ";
}
@@ -1018,22 +1010,28 @@
String dataElementId1, String programStageId2, String dataElementId2, String compareSide, String startDate,
String endDate )
{
- return " EXISTS ( SELECT * FROM ( SELECT * FROM patientdatavalue _pdv INNER JOIN programstageinstance _psi "
- + " ON _pdv.programstageinstanceid=_psi.programstageinstanceid "
+ return " EXISTS ( SELECT * FROM ( SELECT _psi.programstageinstanceid FROM patientdatavalue _pdv "
+ + " INNER JOIN programstageinstance _psi ON _pdv.programstageinstanceid=_psi.programstageinstanceid "
+ " JOIN programinstance _pi ON _pi.programinstanceid=_psi.programinstanceid "
- + " WHERE psi.programstageinstanceid=_pdv.programstageinstanceid and _pdv.dataelementid= " + dataElementId1
- + " AND _psi.organisationunitid in ("+ TextUtils.getCommaDelimitedString( orgunitIds )+") "
+ + " WHERE psi.programstageinstanceid=_pdv.programstageinstanceid AND _pdv.dataelementid= "
+ + dataElementId1
+ + " AND _psi.organisationunitid in ("
+ + TextUtils.getCommaDelimitedString( orgunitIds )
+ + ") "
+ " AND _psi.programstageid = " + programStageId1
- + " AND _psi.executionDate>='" + startDate + "' "
- + " AND _psi.executionDate <= '" + endDate + "' ) AS d1 cross join "
- + " ( SELECT * FROM patientdatavalue _pdv INNER JOIN programstageinstance _psi "
+ + " AND (_psi.executionDate BETWEEN '" + startDate + "' AND '" + endDate
+ + "') ) AS d1 cross join "
+ + " ( SELECT _psi.programstageinstanceid FROM patientdatavalue _pdv INNER JOIN programstageinstance _psi "
+ " ON _pdv.programstageinstanceid=_psi.programstageinstanceid "
+ " JOIN programinstance _pi ON _pi.programinstanceid=_psi.programinstanceid "
- + " WHERE psi.programstageinstanceid=_pdv.programstageinstanceid and _pdv.dataelementid= " + dataElementId2
- + " AND _psi.organisationunitid in ("+ TextUtils.getCommaDelimitedString( orgunitIds )+") "
+ + " WHERE psi.programstageinstanceid=_pdv.programstageinstanceid and _pdv.dataelementid= "
+ + dataElementId2
+ + " AND _psi.organisationunitid in ("
+ + TextUtils.getCommaDelimitedString( orgunitIds )
+ + ") "
+ " AND _psi.programstageid = " + programStageId2
- + " AND _psi.executionDate>='" + startDate + "' "
- + " AND _psi.executionDate <= '" + endDate + "' ) AS d2 WHERE DATE(d1.value ) - DATE(d2.value) " + compareSide;
+ + " AND( _psi.executionDate BETWEEN '" + startDate + "' AND '" + endDate
+ + "') ) AS d2 WHERE DATE(d1.value ) - DATE(d2.value) " + compareSide;
}
/**
@@ -1045,9 +1043,9 @@
{
String sql = "(select organisationunitid from programstageinstance where executiondate>= '" + startDate
+ "' and executiondate<='" + endDate + "')";
- sql += " UNION ";
- sql += "( select distinct organisationunitid from patient where registrationdate>='" + startDate
- + "' and registrationdate<='" + endDate + "')";
+ sql += " UNION ALL ";
+ sql += "( select distinct organisationunitid from patient where registrationdate BETWEEN '" + startDate
+ + "' AND '" + endDate + "')";
Collection<Integer> orgunitIds = new HashSet<Integer>();
orgunitIds = jdbcTemplate.query( sql, new RowMapper<Integer>()
