Fixed 9641: Improve performance of generated window navigation
authorStefan Hühner <stefan.huehner@openbravo.com>
Thu, 25 Jun 2009 16:55:10 +0200
changeset 4064 79d24670c339
parent 4063 858c73b68f2c
child 4065 49a22c0c73fb
Fixed 9641: Improve performance of generated window navigation
- Move rownum on oracle to better location
- Embedd limit/offset/rownum parameter values into sql-string instead of using preparedstatement parameters
src/org/openbravo/erpCommon/utility/TableSQLData.java
--- a/src/org/openbravo/erpCommon/utility/TableSQLData.java	Thu Jun 25 15:05:11 2009 +0200
+++ b/src/org/openbravo/erpCommon/utility/TableSQLData.java	Thu Jun 25 16:55:10 2009 +0200
@@ -1518,8 +1518,8 @@
    *          String identifying tableName.fieldName, this is maintained through recursivity
    * @throws Exception
    */
-  void identifier(String parentTableName, Properties field, String identifierName,
-      String realName) throws Exception {
+  void identifier(String parentTableName, Properties field, String identifierName, String realName)
+      throws Exception {
     String reference;
     if (field == null)
       return;
@@ -2478,38 +2478,24 @@
         txtAuxWhere.append(")\n");
       if (hasRange) {
         // wrap end SQL
+        // calc positions
+        String rangeStart = Integer.toString(startPosition + 1);
+        String rangeEnd = Integer.toString(startPosition + rangeLength);
         if (getPool().getRDBMS().equalsIgnoreCase("ORACLE")) {
-          txtAuxWhere.append("A)\n");
+          txtAuxWhere.append("A");
+          if (hasRangeLimit)
+            txtAuxWhere.append(" WHERE ROWNUM <= " + rangeEnd);
+          txtAuxWhere.append(")\n");
           txtAuxWhere.append("  WHERE rn1 ");
           if (hasRangeLimit)
-            txtAuxWhere.append("BETWEEN ? AND ?");
+            txtAuxWhere.append("BETWEEN " + rangeStart + " AND " + rangeEnd);
           else
             txtAuxWhere.append(">= ?");
           txtAuxWhere.append(")\n");
         } else {
           if (hasRangeLimit)
-            txtAuxWhere.append(" LIMIT TO_NUMBER(?)");
-          txtAuxWhere.append(" OFFSET TO_NUMBER(?))\n");
-        }
-        // wrap parameters
-        try {
-          if (getPool().getRDBMS().equalsIgnoreCase("ORACLE")) {
-            addWrapperParameter("ORAstartPosition", "RANGE", "");
-            setParameter("ORAstartPosition", Integer.toString(startPosition + 1));
-            if (hasRangeLimit) {
-              addWrapperParameter("ORArangeLimit", "RANGE", "");
-              setParameter("ORArangeLimit", Integer.toString(startPosition + rangeLength));
-            }
-          } else {
-            if (hasRangeLimit) {
-              addWrapperParameter("PGrangeLength", "RANGE", "");
-              setParameter("PGrangeLength", Integer.toString(rangeLength));
-              addWrapperParameter("PGstart", "RANGE", "");
-              setParameter("PGstart", Integer.toString(startPosition));
-            }
-          }
-        } catch (Exception e) {
-          e.printStackTrace();
+            txtAuxWhere.append(" LIMIT " + Integer.toString(rangeLength));
+          txtAuxWhere.append(" OFFSET " + Integer.toString(startPosition) + ")\n");
         }
       }