← Back to team overview

dhis2-devs team mailing list archive

[Branch ~dhis2-devs-core/dhis2/trunk] Rev 15138: Resource table, using sql 'insert into select from' statements for improved performance

 

------------------------------------------------------------
revno: 15138
committer: Lars Helge Overland <larshelge@xxxxxxxxx>
branch nick: dhis2
timestamp: Sun 2014-05-04 11:55:24 +0200
message:
  Resource table, using sql 'insert into select from' statements for improved performance
modified:
  dhis-2/dhis-api/src/main/java/org/hisp/dhis/resourcetable/ResourceTableStore.java
  dhis-2/dhis-services/dhis-service-administration/src/main/java/org/hisp/dhis/resourcetable/DefaultResourceTableService.java
  dhis-2/dhis-services/dhis-service-administration/src/main/java/org/hisp/dhis/resourcetable/jdbc/JdbcResourceTableStore.java
  dhis-2/dhis-web/dhis-web-api/src/main/java/org/hisp/dhis/api/controller/ResourceTableController.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-api/src/main/java/org/hisp/dhis/resourcetable/ResourceTableStore.java'
--- dhis-2/dhis-api/src/main/java/org/hisp/dhis/resourcetable/ResourceTableStore.java	2014-03-18 08:10:10 +0000
+++ dhis-2/dhis-api/src/main/java/org/hisp/dhis/resourcetable/ResourceTableStore.java	2014-05-04 09:55:24 +0000
@@ -82,6 +82,8 @@
      * @param groupSets the group sets.
      */
     void createDataElementGroupSetStructure( List<DataElementGroupSet> groupSets );
+    
+    void populateDataElementGroupSetStructure( List<DataElementGroupSet> groupSets );
 
     /**
      * Creates table.
@@ -90,6 +92,8 @@
      */
     void createIndicatorGroupSetStructure( List<IndicatorGroupSet> groupSets );
     
+    void populateIndicatorGroupSetStructure( List<IndicatorGroupSet> groupSets );
+    
     /**
      * Creates table.
      * 
@@ -97,6 +101,8 @@
      */
     void createOrganisationUnitGroupSetStructure( List<OrganisationUnitGroupSet> groupSets );
     
+    void populateOrganisationUnitGroupSetStructure( List<OrganisationUnitGroupSet> groupSets );
+    
     /**
      * Creates table.
      * 

=== modified file 'dhis-2/dhis-services/dhis-service-administration/src/main/java/org/hisp/dhis/resourcetable/DefaultResourceTableService.java'
--- dhis-2/dhis-services/dhis-service-administration/src/main/java/org/hisp/dhis/resourcetable/DefaultResourceTableService.java	2014-04-06 13:41:19 +0000
+++ dhis-2/dhis-services/dhis-service-administration/src/main/java/org/hisp/dhis/resourcetable/DefaultResourceTableService.java	2014-05-04 09:55:24 +0000
@@ -53,16 +53,12 @@
 import org.hisp.dhis.dataelement.DataElementCategoryOption;
 import org.hisp.dhis.dataelement.DataElementCategoryOptionCombo;
 import org.hisp.dhis.dataelement.DataElementCategoryService;
-import org.hisp.dhis.dataelement.DataElementGroup;
 import org.hisp.dhis.dataelement.DataElementGroupSet;
 import org.hisp.dhis.dataelement.DataElementService;
 import org.hisp.dhis.dataset.DataSet;
-import org.hisp.dhis.indicator.Indicator;
-import org.hisp.dhis.indicator.IndicatorGroup;
 import org.hisp.dhis.indicator.IndicatorGroupSet;
 import org.hisp.dhis.indicator.IndicatorService;
 import org.hisp.dhis.organisationunit.OrganisationUnit;
-import org.hisp.dhis.organisationunit.OrganisationUnitGroup;
 import org.hisp.dhis.organisationunit.OrganisationUnitGroupService;
 import org.hisp.dhis.organisationunit.OrganisationUnitGroupSet;
 import org.hisp.dhis.organisationunit.OrganisationUnitService;
@@ -73,9 +69,6 @@
 import org.hisp.dhis.period.PeriodType;
 import org.hisp.dhis.resourcetable.statement.CreateCategoryOptionGroupSetTableStatement;
 import org.hisp.dhis.resourcetable.statement.CreateCategoryTableStatement;
-import org.hisp.dhis.resourcetable.statement.CreateDataElementGroupSetTableStatement;
-import org.hisp.dhis.resourcetable.statement.CreateIndicatorGroupSetTableStatement;
-import org.hisp.dhis.resourcetable.statement.CreateOrganisationUnitGroupSetTableStatement;
 import org.hisp.dhis.sqlview.SqlView;
 import org.hisp.dhis.sqlview.SqlViewService;
 import org.springframework.transaction.annotation.Transactional;
@@ -284,45 +277,13 @@
     @Transactional
     public void generateDataElementGroupSetTable()
     {
-        // ---------------------------------------------------------------------
-        // Create table
-        // ---------------------------------------------------------------------
-
-        List<DataElement> dataElements = new ArrayList<DataElement>( dataElementService.getAllDataElements() );
-        
-        Collections.sort( dataElements, IdentifiableObjectNameComparator.INSTANCE );
-        
         List<DataElementGroupSet> groupSets = new ArrayList<DataElementGroupSet>( dataElementService.getAllDataElementGroupSets() );
         
         Collections.sort( groupSets, IdentifiableObjectNameComparator.INSTANCE );
         
         resourceTableStore.createDataElementGroupSetStructure( groupSets );
 
-        // ---------------------------------------------------------------------
-        // Populate table
-        // ---------------------------------------------------------------------
-
-        List<Object[]> batchArgs = new ArrayList<Object[]>();
-        
-        for ( DataElement dataElement : dataElements )
-        {
-            List<Object> values = new ArrayList<Object>();
-
-            values.add( dataElement.getId() );
-            values.add( dataElement.getName() );
-            
-            for ( DataElementGroupSet groupSet : groupSets )
-            {
-                DataElementGroup group = groupSet.getGroup( dataElement );
-                
-                values.add( group != null ? group.getName() : null );
-                values.add( group != null ? group.getUid() : null );
-            }
-            
-            batchArgs.add( values.toArray() );
-        }
-        
-        resourceTableStore.batchUpdate( ( groupSets.size() * 2 ) + 2, CreateDataElementGroupSetTableStatement.TABLE_NAME, batchArgs );
+        resourceTableStore.populateDataElementGroupSetStructure( groupSets );
         
         log.info( "Data element group set table generated" );
     }
@@ -334,45 +295,13 @@
     @Transactional
     public void generateIndicatorGroupSetTable()
     {
-        // ---------------------------------------------------------------------
-        // Create table
-        // ---------------------------------------------------------------------
-
-        List<Indicator> indicators = new ArrayList<Indicator>( indicatorService.getAllIndicators() );
-        
-        Collections.sort( indicators, IdentifiableObjectNameComparator.INSTANCE );
-        
         List<IndicatorGroupSet> groupSets = new ArrayList<IndicatorGroupSet>( indicatorService.getAllIndicatorGroupSets() );
         
         Collections.sort( groupSets, IdentifiableObjectNameComparator.INSTANCE );
         
         resourceTableStore.createIndicatorGroupSetStructure( groupSets );
 
-        // ---------------------------------------------------------------------
-        // Populate table
-        // ---------------------------------------------------------------------
-
-        List<Object[]> batchArgs = new ArrayList<Object[]>();
-        
-        for ( Indicator indicator : indicators )
-        {
-            List<Object> values = new ArrayList<Object>();
-
-            values.add( indicator.getId() );
-            values.add( indicator.getName() );
-            
-            for ( IndicatorGroupSet groupSet : groupSets )
-            {
-                IndicatorGroup group = groupSet.getGroup( indicator );
-                
-                values.add( group != null ? group.getName() : null );
-                values.add( group != null ? group.getUid() : null );
-            }
-            
-            batchArgs.add( values.toArray() );
-        }
-        
-        resourceTableStore.batchUpdate( ( groupSets.size() * 2 ) + 2, CreateIndicatorGroupSetTableStatement.TABLE_NAME, batchArgs );
+        resourceTableStore.populateIndicatorGroupSetStructure( groupSets );
         
         log.info( "Indicator group set table generated" );
     }
@@ -384,15 +313,6 @@
     @Transactional
     public void generateOrganisationUnitGroupSetTable()
     {
-        // ---------------------------------------------------------------------
-        // Create table
-        // ---------------------------------------------------------------------
-
-        List<OrganisationUnit> units = new ArrayList<OrganisationUnit>( organisationUnitService
-            .getAllOrganisationUnits() );
-
-        Collections.sort( units, IdentifiableObjectNameComparator.INSTANCE );
-
         List<OrganisationUnitGroupSet> groupSets = new ArrayList<OrganisationUnitGroupSet>(
             organisationUnitGroupService.getAllOrganisationUnitGroupSets() );
 
@@ -400,31 +320,7 @@
 
         resourceTableStore.createOrganisationUnitGroupSetStructure( groupSets );
 
-        // ---------------------------------------------------------------------
-        // Populate table
-        // ---------------------------------------------------------------------
-
-        List<Object[]> batchArgs = new ArrayList<Object[]>();
-        
-        for ( OrganisationUnit unit : units )
-        {
-            List<Object> values = new ArrayList<Object>();
-
-            values.add( unit.getId() );
-            values.add( unit.getName() );
-
-            for ( OrganisationUnitGroupSet groupSet : groupSets )
-            {
-                OrganisationUnitGroup group = groupSet.getGroup( unit );
-                
-                values.add( group != null ? group.getName() : null );
-                values.add( group != null ? group.getUid() : null );
-            }
-
-            batchArgs.add( values.toArray() );
-        }
-
-        resourceTableStore.batchUpdate( ( groupSets.size() * 2 ) + 2, CreateOrganisationUnitGroupSetTableStatement.TABLE_NAME, batchArgs );
+        resourceTableStore.populateOrganisationUnitGroupSetStructure( groupSets );
         
         log.info( "Organisation unit group set table generated" );
     }

=== modified file 'dhis-2/dhis-services/dhis-service-administration/src/main/java/org/hisp/dhis/resourcetable/jdbc/JdbcResourceTableStore.java'
--- dhis-2/dhis-services/dhis-service-administration/src/main/java/org/hisp/dhis/resourcetable/jdbc/JdbcResourceTableStore.java	2014-04-09 18:10:48 +0000
+++ dhis-2/dhis-services/dhis-service-administration/src/main/java/org/hisp/dhis/resourcetable/jdbc/JdbcResourceTableStore.java	2014-05-04 09:55:24 +0000
@@ -46,6 +46,7 @@
 import org.hisp.dhis.resourcetable.statement.CreateDataElementGroupSetTableStatement;
 import org.hisp.dhis.resourcetable.statement.CreateIndicatorGroupSetTableStatement;
 import org.hisp.dhis.resourcetable.statement.CreateOrganisationUnitGroupSetTableStatement;
+import org.hisp.dhis.system.util.TextUtils;
 import org.springframework.jdbc.BadSqlGrammarException;
 import org.springframework.jdbc.core.JdbcTemplate;
 
@@ -200,6 +201,35 @@
         jdbcTemplate.execute( statement.getStatement() );
     }
 
+    public void populateDataElementGroupSetStructure( List<DataElementGroupSet> groupSets )
+    {
+        String sql = 
+            "insert into " + CreateDataElementGroupSetTableStatement.TABLE_NAME + " " +
+            "select d.dataelementid as dataelementid, d.name as dataelementname, ";
+        
+        for ( DataElementGroupSet groupSet : groupSets )
+        {
+            sql += "(" +
+                "select deg.name from dataelementgroup deg " +
+                "inner join dataelementgroupmembers degm on degm.dataelementgroupid = deg.dataelementgroupid and degm.dataelementid = d.dataelementid " +
+                "inner join dataelementgroupsetmembers degsm on degsm.dataelementgroupid = degm.dataelementgroupid and degsm.dataelementgroupsetid = " + groupSet.getId() + " " +
+                "limit 1) as " + statementBuilder.columnQuote( groupSet.getName() ) + ", ";
+            
+            sql += "(" +
+                "select deg.uid from dataelementgroup deg " +
+                "inner join dataelementgroupmembers degm on degm.dataelementgroupid = deg.dataelementgroupid and degm.dataelementid = d.dataelementid " +
+                "inner join dataelementgroupsetmembers degsm on degsm.dataelementgroupid = degm.dataelementgroupid and degsm.dataelementgroupsetid = " + groupSet.getId() + " " +
+                "limit 1) as " + statementBuilder.columnQuote( groupSet.getUid() ) + ", ";            
+        }
+
+        sql = TextUtils.removeLastComma( sql ) + " ";
+        sql += "from dataelement d";
+
+        log.info( "Populate data element group set structure SQL: " + sql );
+        
+        jdbcTemplate.execute( sql );
+    }
+    
     // -------------------------------------------------------------------------
     // DataElementGroupSetTable
     // -------------------------------------------------------------------------
@@ -219,6 +249,35 @@
         
         jdbcTemplate.execute( statement.getStatement() );
     }
+
+    public void populateIndicatorGroupSetStructure( List<IndicatorGroupSet> groupSets )
+    {
+        String sql =
+            "insert into " + CreateIndicatorGroupSetTableStatement.TABLE_NAME + " " +
+             "select i.indicatorid as indicatorid, i.name as indicatorname, ";
+        
+        for ( IndicatorGroupSet groupSet : groupSets )
+        {
+            sql += "(" +
+                "select ig.name from indicatorgroup ig " +
+                "inner join indicatorgroupmembers igm on igm.indicatorgroupid = ig.indicatorgroupid and igm.indicatorid = i.indicatorid " +
+                "inner join indicatorgroupsetmembers igsm on igsm.indicatorgroupid = igm.indicatorgroupid and igsm.indicatorgroupsetid = " + groupSet.getId() + " " +
+                "limit 1) as " + statementBuilder.columnQuote( groupSet.getName() ) + ", ";
+
+            sql += "(" +
+                "select ig.uid from indicatorgroup ig " +
+                "inner join indicatorgroupmembers igm on igm.indicatorgroupid = ig.indicatorgroupid and igm.indicatorid = i.indicatorid " +
+                "inner join indicatorgroupsetmembers igsm on igsm.indicatorgroupid = igm.indicatorgroupid and igsm.indicatorgroupsetid = " + groupSet.getId() + " " +
+                "limit 1) as " + statementBuilder.columnQuote( groupSet.getUid() ) + ", ";            
+        }
+
+        sql = TextUtils.removeLastComma( sql ) + " ";
+        sql += "from indicator i";
+
+        log.info( "Populate indicator group set structure SQL: " + sql );
+        
+        jdbcTemplate.execute( sql );
+    }
     
     // -------------------------------------------------------------------------
     // OrganisationUnitGroupSetTable
@@ -239,6 +298,35 @@
         
         jdbcTemplate.execute( statement.getStatement() );
     }
+        
+    public void populateOrganisationUnitGroupSetStructure( List<OrganisationUnitGroupSet> groupSets )
+    {
+        String sql = 
+            "insert into " + CreateOrganisationUnitGroupSetTableStatement.TABLE_NAME + " " +
+            "select ou.organisationunitid as organisationunitid, ou.name as organisationunitname, ";            
+        
+        for ( OrganisationUnitGroupSet groupSet : groupSets )
+        {
+            sql += "(" + 
+                "select oug.name from orgunitgroup oug " +
+                "inner join orgunitgroupmembers ougm on ougm.orgunitgroupid = oug.orgunitgroupid and ougm.organisationunitid = ou.organisationunitid " +
+                "inner join orgunitgroupsetmembers ougsm on ougsm.orgunitgroupid = ougm.orgunitgroupid and ougsm.orgunitgroupsetid = " + groupSet.getId() + " " +
+                "limit 1) as " + statementBuilder.columnQuote( groupSet.getName() ) + ", ";
+            
+            sql += "(" +
+                "select oug.uid from orgunitgroup oug " +
+                "inner join orgunitgroupmembers ougm on ougm.orgunitgroupid = oug.orgunitgroupid and ougm.organisationunitid = ou.organisationunitid " +
+                "inner join orgunitgroupsetmembers ougsm on ougsm.orgunitgroupid = ougm.orgunitgroupid and ougsm.orgunitgroupsetid = " + groupSet.getId() + " " +
+                "limit 1) as " + statementBuilder.columnQuote( groupSet.getUid() ) + ", ";
+        }
+        
+        sql = TextUtils.removeLastComma( sql ) + " ";
+        sql += "from organisationunit ou";
+        
+        log.info( "Populate organisation unit group set structure SQL: " + sql );
+        
+        jdbcTemplate.execute( sql );
+    }
     
     // -------------------------------------------------------------------------
     // CategoryTable

=== modified file 'dhis-2/dhis-web/dhis-web-api/src/main/java/org/hisp/dhis/api/controller/ResourceTableController.java'
--- dhis-2/dhis-web/dhis-web-api/src/main/java/org/hisp/dhis/api/controller/ResourceTableController.java	2014-03-18 08:10:10 +0000
+++ dhis-2/dhis-web/dhis-web-api/src/main/java/org/hisp/dhis/api/controller/ResourceTableController.java	2014-05-04 09:55:24 +0000
@@ -82,11 +82,13 @@
         @RequestParam(required=false) boolean skipResourceTables, 
         @RequestParam(required=false) boolean skipAggregate,
         @RequestParam(required=false) boolean skipEvents,
+        @RequestParam(required=false) boolean last3Years,
         HttpServletResponse response )
     {
         analyticsTableTask.setSkipResourceTables( skipResourceTables );
         analyticsTableTask.setSkipAggregate( skipAggregate );
         analyticsTableTask.setSkipEvents( skipEvents );
+        analyticsTableTask.setLast3Years( last3Years );
         analyticsTableTask.setTaskId( new TaskId( TaskCategory.DATAMART, currentUserService.getCurrentUser() ) );
         
         scheduler.executeTask( analyticsTableTask );