Fixed issue 37214: Improved performance in SQL Query manual window
authorInigo Sanchez <inigo.sanchez@openbravo.com>
Fri, 10 Nov 2017 10:35:18 +0100
changeset 32980 0a1c870b2e2c
parent 32979 273381bbd0b5
child 32981 873652fe3cd4
Fixed issue 37214: Improved performance in SQL Query manual window

It was possible to produce a server crash executing on the sql query window a query returning
thousands of records. An OutOfMemoryError error is raised although only 20 records were showed
to the user because in fact, all the records were retrieving from the db instead of the 20 needed
records.

In order to fix the performance problem, now the queries are executed as a Native SQL with OFFSET
(org.hibernate.SQLQuery.setFirstResult) and LIMIT (org.hibernate.SQLQuery.setMaxResults). Now the
performance problem has been fixed.
src/org/openbravo/erpCommon/ad_forms/SQLExecutor_Query.java
--- a/src/org/openbravo/erpCommon/ad_forms/SQLExecutor_Query.java	Fri Nov 10 10:34:03 2017 +0100
+++ b/src/org/openbravo/erpCommon/ad_forms/SQLExecutor_Query.java	Fri Nov 10 10:35:18 2017 +0100
@@ -19,15 +19,17 @@
 package org.openbravo.erpCommon.ad_forms;
 
 import java.sql.PreparedStatement;
-import java.sql.ResultSet;
 import java.sql.ResultSetMetaData;
 import java.sql.SQLException;
 import java.sql.Types;
+import java.util.List;
 import java.util.Vector;
 
 import javax.servlet.ServletException;
 
 import org.apache.log4j.Logger;
+import org.hibernate.SQLQuery;
+import org.openbravo.dal.service.OBDal;
 import org.openbravo.data.FieldProvider;
 import org.openbravo.database.ConnectionProvider;
 import org.openbravo.exception.NoConnectionAvailableException;
@@ -56,41 +58,42 @@
   public static SQLExecutor_Query[] select(ConnectionProvider connectionProvider, String strSQL,
       int firstRegister, int numberRegisters) throws ServletException {
 
-    PreparedStatement st = null;
-    ResultSet result;
     Vector<SQLExecutor_Query> vector = new Vector<>(0);
 
     try {
       if (log4j.isDebugEnabled())
-        log4j.debug("select - Preparing Statement\n");
-      st = connectionProvider.getPreparedStatement(strSQL);
+        log4j.debug("select - Preparing Native SQL \n");
+      SQLQuery sqlQuery = OBDal.getInstance().getSession().createSQLQuery(strSQL);
+      sqlQuery.setFirstResult(firstRegister);
+      sqlQuery.setMaxResults(numberRegisters);
       if (log4j.isDebugEnabled())
-        log4j.debug("select - Statement Prepared\n");
+        log4j.debug("select - Native SQL Prepared\n");
+
       if (log4j.isDebugEnabled())
-        log4j.debug("select - Executing Statement\n");
-      result = st.executeQuery();
+        log4j.debug("select - Executing Native SQL\n");
+      @SuppressWarnings("unchecked")
+      List<Object> results = sqlQuery.list();
       if (log4j.isDebugEnabled())
-        log4j.debug("select - Statement Executed\n");
-      long countRecord = 0;
-      long countRecordSkip = 1;
-      boolean continueResult = true;
-      while (countRecordSkip < firstRegister && continueResult) {
-        continueResult = result.next();
-        countRecordSkip++;
-      }
-      ResultSetMetaData rmeta = result.getMetaData();
+        log4j.debug("select - Native SQL Executed\n");
+
+      PreparedStatement st = connectionProvider.getPreparedStatement(strSQL);
+      ResultSetMetaData rmeta = st.getMetaData();
       int numColumns = rmeta.getColumnCount();
       Vector<String> types = new Vector<String>(0);
       Vector<String> names = new Vector<String>(0);
       if (log4j.isDebugEnabled())
         log4j.debug("select - Making data\n");
-      while (continueResult && result.next()) {
+
+      int countRecord = 0;
+      for (Object result : results) {
+        Object[] resultFields = (Object[]) result;
         countRecord++;
         SQLExecutor_Query objectSQLExecutor_Query = new SQLExecutor_Query();
         for (int i = 1; i <= numColumns; i++) {
+
           String aux = "";
           try {
-            aux = result.getString(i);
+            aux = resultFields[i].toString();
           } catch (Exception ignored) {
           }
           if (aux == null)
@@ -110,15 +113,9 @@
         }
         types = objectSQLExecutor_Query.type;
         names = objectSQLExecutor_Query.name;
+        vector.addElement(objectSQLExecutor_Query);
+      }
 
-        vector.addElement(objectSQLExecutor_Query);
-        if (countRecord >= numberRegisters && numberRegisters != 0) {
-          continueResult = false;
-        }
-      }
-      if (log4j.isDebugEnabled())
-        log4j.debug("select - Closing resultset\n");
-      result.close();
     } catch (NoConnectionAvailableException ex) {
       log4j.error("No connection available error in query: " + strSQL + "Exception:" + ex);
       throw new ServletException("@CODE=NoConnectionAvailable");
@@ -128,12 +125,7 @@
           + ex2.getMessage());
     } catch (Exception ex3) {
       log4j.error("Error in query: " + strSQL + "Exception:" + ex3);
-      throw new ServletException("@CODE=@" + ex3.getMessage());
-    } finally {
-      try {
-        connectionProvider.releasePreparedStatement(st);
-      } catch (Exception ignored) {
-      }
+      throw new ServletException("@CODE=@" + ex3.getMessage() + ": " + ex3.getCause().getMessage());
     }
     SQLExecutor_Query objectSQLExecutor_Query[] = new SQLExecutor_Query[vector.size()];
     vector.copyInto(objectSQLExecutor_Query);