Fixed 4038: Improve SqlC performance at compile time
authorStefan Hühner <stefan.huehner@openbravo.com>
Tue, 13 Oct 2009 10:18:40 +0200
changeset 5218 98441eb82542
parent 5217 c5b024720ede
child 5219 71f86f731b8a
Fixed 4038: Improve SqlC performance at compile time
- Include optional parameters in SqlC query execution at compile time as this
leads to better performance in many cases
- This requires the optional parameters to be valid parts of an SQL statement
- New behavior can be configured via new Openbravo.properties setting:
sqlc.queryExecutionStrategy=traditional,optimized
- Enable optimizedby default (in template and when new setting is missing)
- Enable for processing src/ folder. Disable by default for srcAD/ and modules/
to not impose stricter requirements on modules.
config/Openbravo.properties.template
src-core/src/org/openbravo/data/Sqlc.java
src/build.xml
src/buildAD.xml
--- a/config/Openbravo.properties.template	Fri Oct 09 18:00:30 2009 +0200
+++ b/config/Openbravo.properties.template	Tue Oct 13 10:18:40 2009 +0200
@@ -96,6 +96,12 @@
 # use js/css minimization (in local-context and war-file)
 minimizeJSandCSS=yes
 
+# How sqlc should execute the queries embedded in xsql file at compilation time
+# traditional: only uses the base query ignoring all optional parameters
+# optimized: includes most optional parameter to achieve better performance
+# If optimized fails for some xsql files, downgrade level to traditional
+sqlc.queryExecutionStrategy=optimized
+
 ##################
 # Authentication #
 ##################
--- a/src-core/src/org/openbravo/data/Sqlc.java	Fri Oct 09 18:00:30 2009 +0200
+++ b/src-core/src/org/openbravo/data/Sqlc.java	Tue Oct 13 10:18:40 2009 +0200
@@ -58,6 +58,7 @@
   String strDBUser;
   String strDBPassword;
   String javaDateFormat;
+  String queryExecutionStrategy;
   static String javaFileName;
   Connection connection;
   Sql sql;
@@ -79,6 +80,9 @@
   StringBuffer buffer;
   static ArrayList<String> includeDirectories;
   static int errorNum;
+  private static boolean queryWithOptionalParameterTypeNone = false;
+  private static boolean queryWithOptionalParameterWithoutType = false;
+  private static boolean queryWithOptionalParameterTypeArgument = false;
 
   static Logger log4j = Logger.getLogger(Sqlc.class); // log4j
 
@@ -156,6 +160,13 @@
 
     sqlc.connect(strFileConnection);
 
+    // use specified queryExecutionModel
+    if (sqlc.queryExecutionStrategy.equals("optimized")) {
+      queryWithOptionalParameterWithoutType = true;
+      queryWithOptionalParameterTypeNone = true;
+      queryWithOptionalParameterTypeArgument = true;
+    }
+
     final File path = new File(dirIni);
     if (!path.exists()) {
       log4j.error("Directory does not exist: " + dirIni);
@@ -244,7 +255,7 @@
           if (parse)
             parseSqlFile(list[i], sqlc, parser, strFilter, fileFin, parent);
         } catch (final IOException e) {
-          log4j.error("IOException: " + e);
+          log4j.error("IOException: ", e);
         }
       }
     }
@@ -319,11 +330,11 @@
         try {
           parser.parse(new InputSource(new FileReader(fileParsing)));
         } catch (final IOException e) {
-          e.printStackTrace();
+          log4j.error("Error parsing xsql file", e);
         } catch (final SAXException e) {
-          e.printStackTrace();
+          log4j.error("Error parsing xsql file", e);
         } catch (final Exception e) {
-          e.printStackTrace();
+          log4j.error("Error parsing xsql file", e);
         }
         if (!sqlc.first) {
           sqlc.printEndClass();
@@ -354,8 +365,7 @@
           log4j.debug("File: " + fileParsing + " \tskipped");
       }
     } catch (final IOException e) {
-      e.printStackTrace();
-      log4j.error("Problem at close of the file");
+      log4j.error("Problem closing the file", e);
     }
   }
 
@@ -508,7 +518,7 @@
         try {
           printFunctionConstant();
         } catch (final IOException ex) {
-          ex.printStackTrace();
+          log4j.error("Error in printFunctionConstant", ex);
         }
       } else {
         query();
@@ -517,13 +527,13 @@
           try {
             printInitClass();
           } catch (final IOException ex) {
-            ex.printStackTrace();
+            log4j.error("Error in printInitClass", ex);
           }
         }
         try {
           printFunctionSql();
         } catch (final IOException ex) {
-          ex.printStackTrace();
+          log4j.error("Error in printFunctionSql", ex);
         }
       }
       sql = new Sql();
@@ -565,10 +575,20 @@
       strDBPassword = properties.getProperty("bbdd.password");
       if (properties.getProperty("bbdd.rdbms").equalsIgnoreCase("POSTGRE"))
         strURL += "/" + properties.getProperty("bbdd.sid");
+      // read from properties file
+      queryExecutionStrategy = properties.getProperty("sqlc.queryExecutionStrategy");
+      // override with value passed from command line/build.xml invocation
+      queryExecutionStrategy = System.getProperty("sqlc.queryExecutionStrategy",
+          queryExecutionStrategy);
+      // if strategy is not set, default to optimized
+      if (queryExecutionStrategy == null) {
+        queryExecutionStrategy = "optimized";
+      }
     } catch (final IOException e) {
-      e.printStackTrace();
+      log4j.error("Error reading propery file", e);
     }
 
+    log4j.info("QueryExecutionStrategy: " + queryExecutionStrategy);
     log4j.info("Loading driver: " + strDriver);
     Class.forName(strDriver);
     log4j.info("Driver loaded");
@@ -588,12 +608,58 @@
   }
 
   private void query() {
+    // try to build a more complete query string, but appending the optional parameters where this
+    // is possible, this should lead to improved execution time of the query and compile time (here
+    // in Sqlc)
+    StringBuilder querySql = new StringBuilder();
+
+    int posSQL = 0;
+    for (final Parameter parameter : sql.vecParameter) {
+      if (parameter.boolOptional) {
+        if (parameter.strAfter == null) {
+          parameter.strAfter = "WHERE";
+          parameter.strText = parameter.strName + " = ? AND";
+        }
+        if (parameter.strName != null && !parameter.strInOut.equals("out")) {
+          int posFinalAfter = posFinal(sql.strSQL, parameter.strAfter);
+          if (posFinalAfter != -1) {
+            querySql.append(imprimirSubstring2(sql.strSQL, posSQL, posFinalAfter));
+            posSQL = posFinalAfter;
+            if (parameter.strInOut.equals("none")) {
+              if (queryWithOptionalParameterTypeNone) {
+                querySql.append(parameter.strText).append('\n');
+              }
+            } else if (parameter.strInOut.equals("argument")) {
+              if (queryWithOptionalParameterTypeArgument) {
+                // heuristic: search for strText ending in "_ID in"
+                if (parameter.strText.trim().endsWith("_ID IN")) {
+                  querySql.append(parameter.strText);
+                  querySql.append("('1')");
+                  querySql.append('\n');
+                }
+              }
+            } else if (parameter.strInOut.equals("replace")) {
+              // don't append replace type parameters
+            } else { // without type
+              if (queryWithOptionalParameterWithoutType) {
+                querySql.append(parameter.strText).append('\n');
+              }
+            }
+          } else if (parameter.strInOut.equals("replace")) {
+            // don't append replace type parameters
+          }
+        }
+      }
+    }
+    querySql.append(imprimirSubstring2(sql.strSQL, posSQL, sql.strSQL.length()));
+
     try {
       if (preparedStatement != null)
         preparedStatement.close();
-      preparedStatement = connection.prepareStatement(sql.strSQL);
+      preparedStatement = connection.prepareStatement(querySql.toString());
       if (log4j.isDebugEnabled())
         log4j.debug("Prepared statement: " + sql.strSQL);
+
       /*
        * Commented because it is not a supported operation ResultSetMetaData rsmdPS =
        * preparedStatement.getMetaData (); // Get the number of columns in the result set int
@@ -602,7 +668,11 @@
        */
       int i = 1;
       for (final Parameter parameter : sql.vecParameter) {
-        if (!parameter.boolOptional) {
+        boolean isParameterWithoutType = !parameter.strInOut.equals("out")
+            && !parameter.strInOut.equals("none") && !parameter.strInOut.equals("argument")
+            && !parameter.strInOut.equals("replace");
+        if (!parameter.boolOptional
+            || (parameter.boolOptional && (isParameterWithoutType && queryWithOptionalParameterWithoutType))) {
           if (parameter.type == java.sql.Types.INTEGER) {
             if (parameter.strDefault == null) {
               if (log4j.isDebugEnabled())
@@ -644,13 +714,11 @@
     } catch (final SQLException e) {
       error = true;
       errorNum++;
-      log4j.error("SQL error in query: " + sql.strSQL + "Exception:" + e);
-      e.printStackTrace();
+      log4j.error("SQL error in query: " + querySql, e);
     } catch (final Exception e) {
       error = true;
       errorNum++;
-      log4j.error("Error in query. Exception:" + e);
-      e.printStackTrace();
+      log4j.error("Error in query.", e);
     }
   }
 
@@ -1151,8 +1219,7 @@
                 + ".InitRecordNumber = Integer.toString(firstRegister);\n");
         }
       } catch (final SQLException e) {
-        log4j.error("SQL Exception error:" + e);
-        e.printStackTrace();
+        log4j.error("SQL Exception error:", e);
       }
       if (sql.sqlReturn.equalsIgnoreCase("MULTIPLE")) {
         out2.append("        vector.addElement(object" + sqlcName + ");\n");
@@ -1421,8 +1488,8 @@
     out2.append("}\n");
   }
 
-  private int posFinal(String strSQL, String strPattern) {
-    int index = sql.strSQL.indexOf(strPattern);
+  private static int posFinal(String strSQL, String strPattern) {
+    int index = strSQL.indexOf(strPattern);
     if (index != -1)
       index = index + strPattern.length();
     return index;
@@ -1443,11 +1510,20 @@
     }
   }
 
+  private static StringBuilder imprimirSubstring2(final String strSQL, int posIni, int posFin) {
+    StringBuilder res = new StringBuilder();
+    final String[] strSqlVector = stringToVector(strSQL.substring(posIni, posFin), true);
+    for (int i = 0; i < strSqlVector.length; i++) {
+      res.append(strSqlVector[i]).append('\n');
+    }
+    return res;
+  }
+
   /**
    * Convert a string with the character 0A (10 decimal) in an array of the text separated by this
    * character
    **/
-  private String[] stringToVector(String strSQL, boolean suppressBlankLines) {
+  private static String[] stringToVector(String strSQL, boolean suppressBlankLines) {
     final byte tab[] = { 10 };
     final String strTab = new String(tab);
     final Vector<String> vector = new Vector<String>();
@@ -1517,7 +1593,7 @@
       log4j.info("javaDateFormat: " + javaDateFormat);
     } catch (final IOException e) {
       // catch possible io errors from readLine()
-      e.printStackTrace();
+      log4j.error("Error loading property file", e);
     }
   }
 }
--- a/src/build.xml	Fri Oct 09 18:00:30 2009 +0200
+++ b/src/build.xml	Tue Oct 13 10:18:40 2009 +0200
@@ -149,6 +149,7 @@
     </java>
     <java classname="org.openbravo.data.Sqlc" fork="yes" jvm="${env.JAVA_HOME}/bin/java" maxmemory="${build.maxmemory}" failonerror="true">
       <arg line="'${base.config}'/Openbravo.properties .xsql '${base.modules}' '${build.sqlc}'/src */src" />
+      <jvmarg value="-Dsqlc.queryExecutionStrategy=traditional"/>
       <classpath refid="project.class.path" />
     </java>
     <copy file="${base.src}/buildAD.xml" tofile="${build.AD}/build.xml" />
--- a/src/buildAD.xml	Fri Oct 09 18:00:30 2009 +0200
+++ b/src/buildAD.xml	Tue Oct 13 10:18:40 2009 +0200
@@ -25,6 +25,7 @@
   <target name="buildAD">
     <java classname="org.openbravo.data.Sqlc" fork="yes" jvm="${env.JAVA_HOME}/bin/java" maxmemory="${build.maxmemory}">
       <arg line="'${base.config}'/Openbravo.properties .xsql ../srcAD '${build.sqlc}'/src" />
+      <jvmarg value="-Dsqlc.queryExecutionStrategy=traditional"/>
       <classpath refid="project.class.path" />
     </java>