dhis2-devs team mailing list archive

[noreply@xxxxxxxxxxxxx]

------------------------------------------------------------
revno: 4981
committer: Lars Helge Overland <larshelge@xxxxxxxxx>
branch nick: dhis2
timestamp: Wed 2011-10-19 16:01:02 +0200
message:
  Data mart, retrieving individual named columns rather than all from crosstab table, improves performance by 10% on benchmark db.
modified:
  dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/crosstab/jdbc/JDBCCrossTabStore.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-datamart-default/src/main/java/org/hisp/dhis/datamart/crosstab/jdbc/JDBCCrossTabStore.java'
--- dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/crosstab/jdbc/JDBCCrossTabStore.java	2011-09-23 16:22:15 +0000
+++ dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/crosstab/jdbc/JDBCCrossTabStore.java	2011-10-19 14:01:02 +0000
@@ -117,7 +117,9 @@
     {
         final StatementHolder holder = statementManager.getHolder();
         
-        final String sql = "SELECT * FROM " + CROSSTAB_TABLE_PREFIX + key + " AS c WHERE c.periodid IN (" + 
+        final String operandIds = getCommadelimitedString( operands );
+        
+        final String sql = "SELECT periodid, sourceid, " + operandIds + " FROM " + CROSSTAB_TABLE_PREFIX + key + " AS c WHERE c.periodid IN (" + 
             getCommaDelimitedString( periodIds ) + ") AND c.sourceid IN (" + getCommaDelimitedString( sourceIds ) + ")";
         
         try
@@ -141,7 +143,9 @@
     {
         final StatementHolder holder = statementManager.getHolder();
 
-        final String sql = "SELECT * FROM " + CROSSTAB_TABLE_PREFIX + key + " AS c WHERE c.periodid IN (" + 
+        final String operandIds = getCommadelimitedString( operands );
+        
+        final String sql = "SELECT periodid, sourceid, " + operandIds + " FROM " + CROSSTAB_TABLE_PREFIX + key + " AS c WHERE c.periodid IN (" + 
             getCommaDelimitedString( periodIds ) + ") AND c.sourceid = " + sourceId;
 
         try
@@ -229,4 +233,21 @@
         
         return values;
     }
+    
+    private String getCommadelimitedString( Collection<DataElementOperand> operands )
+    {
+        final StringBuilder builder = new StringBuilder();
+        
+        for ( DataElementOperand operand : operands )
+        {
+            builder.append( operand.getColumnName() ).append( "," );
+        }
+        
+        if ( builder.length() > 0 )
+        {
+            builder.deleteCharAt( builder.length() - 1);
+        }
+        
+        return builder.toString();
+    }
 }