dhis2-devs team mailing list archive

[noreply@xxxxxxxxxxxxx]

------------------------------------------------------------
revno: 18438
committer: Lars Helge Overland <larshelge@xxxxxxxxx>
branch nick: dhis2
timestamp: Thu 2015-02-26 17:45:09 +0100
message:
  Approval, improved performance of sql query by omitting dataset table join
modified:
  dhis-2/dhis-services/dhis-service-core/src/main/java/org/hisp/dhis/dataapproval/hibernate/HibernateDataApprovalStore.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-core/src/main/java/org/hisp/dhis/dataapproval/hibernate/HibernateDataApprovalStore.java'
--- dhis-2/dhis-services/dhis-service-core/src/main/java/org/hisp/dhis/dataapproval/hibernate/HibernateDataApprovalStore.java	2015-02-26 11:19:31 +0000
+++ dhis-2/dhis-services/dhis-service-core/src/main/java/org/hisp/dhis/dataapproval/hibernate/HibernateDataApprovalStore.java	2015-02-26 16:45:09 +0000
@@ -302,8 +302,6 @@
             testAncestors += "or o" + i + ".organisationunitid is not null ";
         }
 
-        final String dsCategoryComboIdMatches = isDefaultCombo ? "" : "and ds.categorycomboid = a.categorycomboid "; // Default option combo matches any data set.
-
         String readyBelowSubquery = "true"; // Ready below if this is the lowest (highest number) approval orgUnit level.
 
         int orgUnitLevelAbove = 0;
@@ -327,10 +325,9 @@
                 boolean acceptanceRequiredForApproval = (Boolean) systemSettingManager.getSystemSetting( KEY_ACCEPTANCE_REQUIRED_FOR_APPROVAL, false );
 
                 readyBelowSubquery = "not exists (select 1 from _orgunitstructure ous " +
-                    "join dataset ds on ds.datasetid in (" + dataSetIds + ") " + dsCategoryComboIdMatches +
                     "left join dataapproval da on da.organisationunitid = ous.organisationunitid " +
                     "and da.dataapprovallevelid = " + dal.getId() + " and da.periodid in (" + periodIds + ") " +
-                    "and da.datasetid = ds.datasetid " +
+                    "and da.datasetid in (" + dataSetIds + ") " +
                     "and da.attributeoptioncomboid = a.categoryoptioncomboid " +
                     "where ous.idlevel" + orgUnitLevel + " = a.organisationunitid " +
                     "and ous.level = " + dal.getOrgUnitLevel() + " " +
@@ -345,23 +342,20 @@
         {
             approvedAboveSubquery = "exists(select 1 from dataapproval da " +
                 "join dataapprovallevel dal on dal.dataapprovallevelid = da.dataapprovallevelid " +
-                "join dataset ds on ds.datasetid = da.datasetid and ds.datasetid in (" + dataSetIds + ") " + dsCategoryComboIdMatches +
                 "join _orgunitstructure ou on ou.organisationunitid = a.organisationunitid and ou.idlevel" + orgUnitLevelAbove + " = da.organisationunitid " +
-                "where da.periodid in (" + periodIds + ") and da.attributeoptioncomboid = a.categoryoptioncomboid) ";
+                "where da.periodid in (" + periodIds + ") and da.datasetid in (" + dataSetIds + ") and da.attributeoptioncomboid = a.categoryoptioncomboid) ";
         }
 
         final String sql =
             "select a.categoryoptioncomboid, a.organisationunitid, " +
             "(select min(coalesce(dal.level, 0)) from period p " +
-                "join dataset ds on ds.datasetid in (" + dataSetIds + ") " + dsCategoryComboIdMatches +
-                "left join dataapproval da on da.datasetid = ds.datasetid and da.periodid = p.periodid " +
+            "left join dataapproval da on da.datasetid in (" + dataSetIds + ") and da.periodid = p.periodid " +
                     "and da.attributeoptioncomboid = a.categoryoptioncomboid and da.organisationunitid = a.organisationunitid " +
                 "left join dataapprovallevel dal on dal.dataapprovallevelid = da.dataapprovallevelid " +
                 "where p.periodid in (" + periodIds + ") " +
             ") as highest_approved_level, " +
             "(select substring(min(concat(100000 + coalesce(dal.level, 0), coalesce(da.accepted, FALSE))) from 7) from period p " +
-                "join dataset ds on ds.datasetid in (" + dataSetIds + ") " + dsCategoryComboIdMatches +
-                "left join dataapproval da on da.datasetid = ds.datasetid and da.periodid = p.periodid " +
+                "left join dataapproval da on da.datasetid in (" + dataSetIds + ") and da.periodid = p.periodid " +
                     "and da.attributeoptioncomboid = a.categoryoptioncomboid and da.organisationunitid = a.organisationunitid " +
                 "left join dataapprovallevel dal on dal.dataapprovallevelid = da.dataapprovallevelid " +
                 "where p.periodid in (" + periodIds + ") " +