dhis2-devs team mailing list archive
-
dhis2-devs team
-
Mailing list archive
-
Message #09945
[Branch ~dhis2-devs-core/dhis2/trunk] Rev 2703: Adding indexes to the aggregated*values tables at the end of the scheduled data mart export proce...
------------------------------------------------------------
revno: 2703
committer: Lars Helge Overland <larshelge@xxxxxxxxx>
branch nick: dhis2
timestamp: Fri 2011-01-21 13:44:09 +0100
message:
Adding indexes to the aggregated*values tables at the end of the scheduled data mart export process. The reason for not having indexes so far is that is actually performs slower on small specific data mart exports like the ones done for report tables due to the overhead of writing to indexed tables / creating and dropping indexes on large tables. When running with batch aggregation strategy it definitely improves performance as this operation is typically done once per day.
modified:
dhis-2/dhis-api/src/main/java/org/hisp/dhis/datamart/DataMartService.java
dhis-2/dhis-services/dhis-service-core/src/main/java/org/hisp/dhis/aggregation/jdbc/JdbcAggregatedDataValueStore.java
dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/engine/DataMartEngine.java
dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/engine/DefaultDataMartEngine.java
dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/impl/DefaultDataMartService.java
dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/task/DataMartTask.java
dhis-2/dhis-web/dhis-web-commons-resources/src/main/webapp/dhis-web-commons/javascripts/commons.js
--
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/datamart/DataMartService.java'
--- dhis-2/dhis-api/src/main/java/org/hisp/dhis/datamart/DataMartService.java 2010-12-11 20:38:50 +0000
+++ dhis-2/dhis-api/src/main/java/org/hisp/dhis/datamart/DataMartService.java 2011-01-21 12:44:09 +0000
@@ -75,6 +75,21 @@
int export( Collection<Integer> dataElementIds, Collection<Integer> indicatorIds,
Collection<Integer> periodIds, Collection<Integer> organisationUnitIds, RelativePeriods relatives );
+ /**
+ * Exports to data mart for the given arguments.
+ *
+ * @param dataElementIds the data element identifiers.
+ * @param indicatorIds the indicator identifiers.
+ * @param periodIds the period identifiers.
+ * @param organisationUnitIds the organisation unit identifiers.
+ * @param relatives the RelativePeriods.
+ * @param useIndexes indicates whether to create indexes on the data mart tables
+ * after the export process.
+ * @return the number of exported values.
+ */
+ int export( Collection<Integer> dataElementIds, Collection<Integer> indicatorIds,
+ Collection<Integer> periodIds, Collection<Integer> organisationUnitIds, RelativePeriods relatives, boolean useIndexes );
+
// ----------------------------------------------------------------------
// DataMartExport
// ----------------------------------------------------------------------
=== modified file 'dhis-2/dhis-services/dhis-service-core/src/main/java/org/hisp/dhis/aggregation/jdbc/JdbcAggregatedDataValueStore.java'
--- dhis-2/dhis-services/dhis-service-core/src/main/java/org/hisp/dhis/aggregation/jdbc/JdbcAggregatedDataValueStore.java 2011-01-21 11:06:16 +0000
+++ dhis-2/dhis-services/dhis-service-core/src/main/java/org/hisp/dhis/aggregation/jdbc/JdbcAggregatedDataValueStore.java 2011-01-21 12:44:09 +0000
@@ -279,14 +279,28 @@
{
if ( dataElement )
{
- final String sql = "CREATE INDEX aggregateddatavalue_index ON aggregateddatavalue (dataelementid, categoryoptioncomboid, periodid, organisationunitid)";
- statementManager.getHolder().executeUpdate( sql );
+ try
+ {
+ final String sql = "CREATE INDEX aggregateddatavalue_index ON aggregateddatavalue (dataelementid, categoryoptioncomboid, periodid, organisationunitid)";
+ statementManager.getHolder().executeUpdate( sql );
+ }
+ catch ( Exception ex )
+ {
+ log.debug( "Index already exists" );
+ }
}
if ( indicator )
{
- final String sql = "CREATE INDEX aggregatedindicatorvalue_index ON aggregatedindicatorvalue (indicatorid, periodid, organisationunitid)";
- statementManager.getHolder().executeUpdate( sql );
+ try
+ {
+ final String sql = "CREATE INDEX aggregatedindicatorvalue_index ON aggregatedindicatorvalue (indicatorid, periodid, organisationunitid)";
+ statementManager.getHolder().executeUpdate( sql );
+ }
+ catch ( Exception ex )
+ {
+ log.debug( "Index already exists" );
+ }
}
}
@@ -294,14 +308,28 @@
{
if ( dataElement )
{
- final String sql = "DROP INDEX aggregateddatavalue_index";
- statementManager.getHolder().executeUpdate( sql );
+ try
+ {
+ final String sql = "DROP INDEX aggregateddatavalue_index";
+ statementManager.getHolder().executeUpdate( sql );
+ }
+ catch ( Exception ex )
+ {
+ log.debug( "Index does not exist" );
+ }
}
if ( indicator )
{
- final String sql = "DROP INDEX aggregatedindicatorvalue_index";
- statementManager.getHolder().executeUpdate( sql );
+ try
+ {
+ final String sql = "DROP INDEX aggregatedindicatorvalue_index";
+ statementManager.getHolder().executeUpdate( sql );
+ }
+ catch ( Exception ex )
+ {
+ log.debug( "Index already exists" );
+ }
}
}
=== modified file 'dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/engine/DataMartEngine.java'
--- dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/engine/DataMartEngine.java 2010-04-12 21:23:33 +0000
+++ dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/engine/DataMartEngine.java 2011-01-21 12:44:09 +0000
@@ -36,6 +36,18 @@
*/
public interface DataMartEngine
{
+ /**
+ * Exports aggregated values to the data mart tables.
+ *
+ * @param dataElementIds the data element identifiers.
+ * @param indicatorIds the indicator identifiers.
+ * @param periodIds the period identifiers.
+ * @param organisationUnitIds the organisation unit identifiers.
+ * @param useIndexes indicates whether to create indexes on the data mart tables
+ * after the export process.
+ * @param processState the state object.
+ * @return the number of values exported to the data mart tables.
+ */
int export( Collection<Integer> dataElementIds, Collection<Integer> indicatorIds,
- Collection<Integer> periodIds, Collection<Integer> organisationUnitIds, ProcessState processState );
+ Collection<Integer> periodIds, Collection<Integer> organisationUnitIds, boolean useIndexes, ProcessState processState );
}
=== modified file 'dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/engine/DefaultDataMartEngine.java'
--- dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/engine/DefaultDataMartEngine.java 2011-01-20 21:08:24 +0000
+++ dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/engine/DefaultDataMartEngine.java 2011-01-21 12:44:09 +0000
@@ -196,7 +196,7 @@
@Transactional
public int export( Collection<Integer> dataElementIds, Collection<Integer> indicatorIds,
- Collection<Integer> periodIds, Collection<Integer> organisationUnitIds, ProcessState state )
+ Collection<Integer> periodIds, Collection<Integer> organisationUnitIds, boolean useIndexes, ProcessState state )
{
int count = 0;
@@ -269,6 +269,17 @@
log.info( "Number of crosstab tables: " + keys.size() + ", " + TimeUtils.getHMS() );
// ---------------------------------------------------------------------
+ // Drop potential indexes
+ // ---------------------------------------------------------------------
+
+ boolean isIndicators = indicators != null && indicators.size() > 0;
+ boolean isCalculatedDataElements = calculatedDataElements != null && calculatedDataElements.size() > 0;
+
+ aggregatedDataValueService.dropIndex( true, isIndicators );
+
+ log.info( "Dropped potential indexes: " + TimeUtils.getHMS() );
+
+ // ---------------------------------------------------------------------
// Delete existing aggregated data
// ---------------------------------------------------------------------
@@ -309,26 +320,26 @@
log.info( "Exported values for data element operands with average aggregation operator of type yes/no: " + TimeUtils.getHMS() );
}
+ // ---------------------------------------------------------------------
+ // Indicator export
+ // ---------------------------------------------------------------------
+
state.setMessage( "exporting_data_for_indicators" );
- // ---------------------------------------------------------------------
- // Indicator export
- // ---------------------------------------------------------------------
-
- if ( indicators != null && indicators.size() > 0 )
+ if ( isIndicators )
{
count += indicatorDataMart.exportIndicatorValues( indicators, periods, organisationUnits, indicatorOperands, keys );
log.info( "Exported values for indicators (" + indicators.size() + "): " + TimeUtils.getHMS() );
}
+ // ---------------------------------------------------------------------
+ // Calculated data element export
+ // ---------------------------------------------------------------------
+
state.setMessage( "exporting_data_for_calculated_data_elements" );
- // ---------------------------------------------------------------------
- // Calculated data element export
- // ---------------------------------------------------------------------
-
- if ( calculatedDataElements != null && calculatedDataElements.size() > 0 )
+ if ( isCalculatedDataElements )
{
count += calculatedDataElementDataMart.exportCalculatedDataElements( calculatedDataElements, periods, organisationUnits, calculatedOperands, keys );
@@ -343,7 +354,20 @@
{
crossTabService.dropCrossTabTable( key );
}
-
+
+ log.info( "Dropped crosstab tables: " + TimeUtils.getHMS() );
+
+ // ---------------------------------------------------------------------
+ // Create potential indexes
+ // ---------------------------------------------------------------------
+
+ if ( useIndexes )
+ {
+ aggregatedDataValueService.createIndex( true, isIndicators );
+
+ log.info( "Created indexes: " + TimeUtils.getHMS() );
+ }
+
log.info( "Export process completed: " + TimeUtils.getHMS() );
TimeUtils.stop();
=== modified file 'dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/impl/DefaultDataMartService.java'
--- dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/impl/DefaultDataMartService.java 2011-01-11 13:11:31 +0000
+++ dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/impl/DefaultDataMartService.java 2011-01-21 12:44:09 +0000
@@ -96,15 +96,15 @@
getIdentifiers( DataElement.class, dataMartExport.getDataElements() ),
getIdentifiers( Indicator.class, dataMartExport.getIndicators() ),
getIdentifiers( Period.class, allPeriods ),
- getIdentifiers( OrganisationUnit.class, dataMartExport.getOrganisationUnits() ),
- new OutputHolderState() );
+ getIdentifiers( OrganisationUnit.class, dataMartExport.getOrganisationUnits() ),
+ false, new OutputHolderState() );
}
@Transactional
public int export( Collection<Integer> dataElementIds, Collection<Integer> indicatorIds,
Collection<Integer> periodIds, Collection<Integer> organisationUnitIds )
{
- return export( dataElementIds, indicatorIds, periodIds, organisationUnitIds, null );
+ return dataMartEngine.export( dataElementIds, indicatorIds, periodIds, organisationUnitIds, false, new OutputHolderState() );
}
@Transactional
@@ -116,7 +116,19 @@
periodIds.addAll( getIdentifiers( Period.class, periodService.reloadPeriods( relatives.getRelativePeriods( 1, null, false ) ) ) );
}
- return dataMartEngine.export( dataElementIds, indicatorIds, periodIds, organisationUnitIds, new OutputHolderState() );
+ return dataMartEngine.export( dataElementIds, indicatorIds, periodIds, organisationUnitIds, false, new OutputHolderState() );
+ }
+
+ @Transactional
+ public int export( Collection<Integer> dataElementIds, Collection<Integer> indicatorIds,
+ Collection<Integer> periodIds, Collection<Integer> organisationUnitIds, RelativePeriods relatives, boolean useIndexes )
+ {
+ if ( relatives != null )
+ {
+ periodIds.addAll( getIdentifiers( Period.class, periodService.reloadPeriods( relatives.getRelativePeriods( 1, null, false ) ) ) );
+ }
+
+ return dataMartEngine.export( dataElementIds, indicatorIds, periodIds, organisationUnitIds, useIndexes, new OutputHolderState() );
}
// -------------------------------------------------------------------------
=== modified file 'dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/task/DataMartTask.java'
--- dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/task/DataMartTask.java 2011-01-19 11:53:21 +0000
+++ dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/task/DataMartTask.java 2011-01-21 12:44:09 +0000
@@ -78,6 +78,6 @@
log.info( "Starting scheduled data mart task" );
- dataMartService.export( dataElementIds, indicatorIds, new HashSet<Integer>(), organisationUnitIds, relatives );
+ dataMartService.export( dataElementIds, indicatorIds, new HashSet<Integer>(), organisationUnitIds, relatives, true );
}
}
=== modified file 'dhis-2/dhis-web/dhis-web-commons-resources/src/main/webapp/dhis-web-commons/javascripts/commons.js'
--- dhis-2/dhis-web/dhis-web-commons-resources/src/main/webapp/dhis-web-commons/javascripts/commons.js 2011-01-21 10:42:03 +0000
+++ dhis-2/dhis-web/dhis-web-commons-resources/src/main/webapp/dhis-web-commons/javascripts/commons.js 2011-01-21 12:44:09 +0000
@@ -738,7 +738,6 @@
if ( result )
{
- setWaitMessage( i18n_process );
$.postJSON(
action,
{
@@ -761,7 +760,6 @@
{
showWarningMessage( json.message );
}
- hideMessage();
}
);
}
