svn commit: r673014 - /ofbiz/trunk/framework/entity/src/org/ofbiz/entity/datasource/GenericDAO.java

[jonesde]

Author: jonesde
Date: Tue Jul  1 00:42:26 2008
New Revision: 673014

URL: http://svn.apache.org/viewvc?rev=673014&view=rev
Log:
Removed hack fix for getting a count of results when there is a GROUP BY in the select; now lets the database do the count instead of iterating through every single result in order to get the count, so is WAY more efficient; note that this also uses the COUNT(1) instead of COUNT(*) approach for better performance, ie getting the same result but asking the database to do less

Modified:
    ofbiz/trunk/framework/entity/src/org/ofbiz/entity/datasource/GenericDAO.java

Modified: ofbiz/trunk/framework/entity/src/org/ofbiz/entity/datasource/GenericDAO.java
URL: http://svn.apache.org/viewvc/ofbiz/trunk/framework/entity/src/org/ofbiz/entity/datasource/GenericDAO.java?rev=673014&r1=673013&r2=673014&view=diff
==============================================================================
--- ofbiz/trunk/framework/entity/src/org/ofbiz/entity/datasource/GenericDAO.java (original)
+++ ofbiz/trunk/framework/entity/src/org/ofbiz/entity/datasource/GenericDAO.java Tue Jul  1 00:42:26 2008
@@ -882,14 +882,36 @@
             // put this inside an if statement so that we don't have to generate the string when not used...
             Debug.logVerbose("Doing selectListIteratorByCondition with whereEntityCondition: " + whereEntityCondition, module);
         }
+        
+        boolean isGroupBy = false;
+        ModelViewEntity modelViewEntity = null;
+        String groupByString = null;
+        if (modelEntity instanceof ModelViewEntity) {
+            modelViewEntity = (ModelViewEntity) modelEntity;
+            groupByString = modelViewEntity.colNameString(modelViewEntity.getGroupBysCopy(), ", ", "", false);
+            if (UtilValidate.isNotEmpty(groupByString)) {
+                isGroupBy = true;                  
+            }
+        }
+        
+        // To get a count of the rows that will be returned when there is a GROUP BY, must do something like:
+        //     SELECT COUNT(1) FROM (SELECT COUNT(1) FROM OFBIZ.POSTAL_ADDRESS PA GROUP BY PA.CITY) TEMP_NAME
+        // instead of a simple:
+        //     SELECT COUNT(1) FROM OFBIZ.POSTAL_ADDRESS PA GROUP BY PA.CITY
 
         StringBuilder sqlBuffer = new StringBuilder("SELECT ");
+        
+        if (isGroupBy) {
+            sqlBuffer.append("COUNT(1) FROM (SELECT ");
+        }
 
         if (findOptions.getDistinct()) {
-            sqlBuffer.append("DISTINCT ");
+            sqlBuffer.append("DISTINCT COUNT(*) ");
+        } else {
+            // NOTE DEJ20080701 Changed from COUNT(*) to COUNT(1) to improve performance, and should get the same results at least when there is no DISTINCT
+            sqlBuffer.append("COUNT(1) ");
         }
 
-        sqlBuffer.append("COUNT(*) ");
 
         // FROM clause and when necessary the JOIN or LEFT JOIN clause(s) as well
         sqlBuffer.append(SqlJdbcUtil.makeFromClause(modelEntity, datasourceInfo));
@@ -922,10 +944,7 @@
         }
 
         // GROUP BY clause for view-entity
-        if (modelEntity instanceof ModelViewEntity) {
-            ModelViewEntity modelViewEntity = (ModelViewEntity) modelEntity;
-            String groupByString = modelViewEntity.colNameString(modelViewEntity.getGroupBysCopy(), ", ", "", false);
-
+        if (isGroupBy) {
             if (UtilValidate.isNotEmpty(groupByString)) {
                 sqlBuffer.append(" GROUP BY ");
                 sqlBuffer.append(groupByString);
@@ -942,8 +961,13 @@
             sqlBuffer.append(" HAVING ");
             sqlBuffer.append(entityCondHavingString);
         }
+        
+        if (isGroupBy) {
+            sqlBuffer.append(") TEMP_NAME");
+        }
 
         String sql = sqlBuffer.toString();
+        Debug.logInfo("Count select sql: " + sql, module);
 
         SQLProcessor sqlP = new SQLProcessor(helperName);
         sqlP.prepareStatement(sql, findOptions.getSpecifyTypeAndConcur(), findOptions.getResultSetType(),
@@ -970,19 +994,7 @@
             sqlP.executeQuery();
             long count = 0;
             ResultSet resultSet = sqlP.getResultSet();
-            boolean isGroupBy = false;
-            if (modelEntity instanceof ModelViewEntity) {
-                ModelViewEntity modelViewEntity = (ModelViewEntity) modelEntity;
-                String groupByString = modelViewEntity.colNameString(modelViewEntity.getGroupBysCopy(), ", ", "", false);
-                if (UtilValidate.isNotEmpty(groupByString)) {
-                    isGroupBy = true;                  
-                }
-            }
-            if (isGroupBy) {
-                while (resultSet.next()) {
-                    count++;
-                }
-            } else if (resultSet.next()) {
+            if (resultSet.next()) {
                 count = resultSet.getLong(1);
             }
             return count;