Fixed 9562: Optimize product selector on oracle by better placement of the rownum where clause
authorStefan Hühner <stefan.huehner@openbravo.com>
Wed, 24 Jun 2009 16:34:46 +0200
changeset 4059 cc9c20208b2e
parent 4058 b8145e69620e
child 4060 f867ecc1b1e2
child 4109 694ed8e283d2
Fixed 9562: Optimize product selector on oracle by better placement of the rownum where clause
src/org/openbravo/erpCommon/info/Product.java
src/org/openbravo/erpCommon/info/Product_data.xsql
--- a/src/org/openbravo/erpCommon/info/Product.java	Wed Jun 24 12:47:40 2009 +0200
+++ b/src/org/openbravo/erpCommon/info/Product.java	Wed Jun 24 16:34:46 2009 +0200
@@ -148,7 +148,7 @@
       vars.setSessionValue("Product.priceListVersion", strPriceListVersion);
       ProductData[] data = ProductData.select(this, strWarehouse, "1", strKeyValue + "%", "",
           Utility.getContext(this, vars, "#User_Client", "Product"), Utility.getContext(this, vars,
-              "#User_Org", "Product"), strPriceListVersion, "1", "", "");
+              "#User_Org", "Product"), strPriceListVersion, "1", "", "", "");
       if (data != null && data.length == 1)
         printPageKey(response, vars, data, strWarehouse, strPriceListVersion);
       else
@@ -372,15 +372,16 @@
 
         // Filtering result
         if (this.myPool.getRDBMS().equalsIgnoreCase("ORACLE")) {
-          String oraLimit = (offset + 1) + " AND " + String.valueOf(offset + pageSize);
+          String oraLimit1 = String.valueOf(offset + pageSize);
+          String oraLimit2 = (offset + 1) + " AND " + oraLimit1;
           data = ProductData.select(this, strWarehouse, "ROWNUM", strKey, strName, Utility
               .getContext(this, vars, "#User_Client", "Product"), Utility.getSelectorOrgs(this,
-              vars, strOrg), strPriceListVersion, strOrderBy, oraLimit, "");
+              vars, strOrg), strPriceListVersion, strOrderBy, oraLimit1, oraLimit2, "");
         } else {
           String pgLimit = pageSize + " OFFSET " + offset;
           data = ProductData.select(this, strWarehouse, "1", strKey, strName, Utility.getContext(
               this, vars, "#User_Client", "Product"), Utility.getSelectorOrgs(this, vars, strOrg),
-              strPriceListVersion, strOrderBy, "", pgLimit);
+              strPriceListVersion, strOrderBy, "", "", pgLimit);
         }
       } catch (ServletException e) {
         log4j.error("Error in print page data: " + e);
--- a/src/org/openbravo/erpCommon/info/Product_data.xsql	Wed Jun 24 12:47:40 2009 +0200
+++ b/src/org/openbravo/erpCommon/info/Product_data.xsql	Wed Jun 24 16:34:46 2009 +0200
@@ -79,7 +79,8 @@
         <Parameter name="adUserOrg" type="replace" optional="true" after="ip.AD_Org_ID IN (" text="'1'"/>
         <Parameter name="priceListVersion"/>
         <Parameter name="orderBy" type="replace" optional="true" after="ORDER BY " text="ip.Value" />
-        <Parameter name="oraLimit" type="argument" optional="true" after="WHERE 1=1"><![CDATA[AND RN1 BETWEEN ]]></Parameter>
+        <Parameter name="oraLimit1" type="argument" optional="true" after=") C"><![CDATA[ WHERE ROWNUM <= ]]></Parameter>
+        <Parameter name="oraLimit2" type="argument" optional="true" after="WHERE 1=1"><![CDATA[AND RN1 BETWEEN ]]></Parameter>
         <Parameter name="pgLimit" type="argument" optional="true" after="WHERE 1=1"><![CDATA[LIMIT ]]></Parameter>
         <Parameter name="priceListVersion"/>
         <Parameter name="orderBy" type="replace" optional="true" after="A ORDER BY " text="Value" />