dhis2-devs team mailing list archive
-
dhis2-devs team
-
Mailing list archive
-
Message #29828
[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 );
