Changeset 19582

Timestamp:

Sep 16, 2008, 10:09:43 AM (18 years ago)

Author:

Paul Price

Message:

Optimising difftool -inputskyfile: this was driving the load on the mysql server way high. The problem is that there were two sub-selects that are UNION-ed, and then the search criteria were applied. The new version applies the search criteria to each sub-select separately, resulting in about a 20-fold reduction in execution time. Main patch by Josh, with a small fix by me.

Location:

trunk/ippTools

Files:

• share/difftool_inputskyfile.sql (modified) (2 diffs)
• src/difftool.c (modified) (2 diffs)

trunk/ippTools/share/difftool_inputskyfile.sql

@@ -35 +35 @@
         AND camRun.state = 'full'
         AND chipRun.state = 'full'
+        -- where hook %s
     UNION
     SELECT 
@@ -71 +72 @@
         AND camRun.state = 'full'
         AND chipRun.state = 'full'
+        -- where hook %s
     ) as Foo

trunk/ippTools/src/difftool.c

@@ -318 +318 @@
     }
 
+    psString whereClause = NULL;
     if (psListLength(where->list)) {
-        psString whereClause = psDBGenerateWhereConditionSQL(where, NULL);
-        psStringAppend(&query, " WHERE %s", whereClause);
-        psFree(whereClause);
-    }
+        whereClause = psDBGenerateWhereConditionSQL(where, NULL);
+        psStringPrepend(&whereClause, "\n AND ");
+    }
+    psFree(where);
 
     // Add condition to get only templates or only inputs
     {
-        psString whereClause = NULL;
+        psString templateClause = NULL;
         if (template) {
-            psStringAppend(&whereClause, " %s", " template != 0");
+            psStringAppend(&templateClause, " %s", " template != 0");
         } else if (input) {
-            psStringAppend(&whereClause, " %s", " template = 0");
-        }
-        if (whereClause) {
-            psStringAppend(&query, " %s %s", psListLength(where->list) ? "AND" : "WHERE", whereClause);
-        }
-        psFree(whereClause);
-    }
-
-    psFree(where);
+            psStringAppend(&templateClause, " %s", " template = 0");
+        }
+        if (templateClause) {
+            psStringAppend(&whereClause, "\n AND %s", templateClause);
+        }
+        psFree(templateClause);
+    }
+
 
     // treat limit == 0 as "no limit"
@@ -347 +347 @@
     }
 
-    if (!p_psDBRunQuery(config->dbh, query)) {
-        psError(PS_ERR_UNKNOWN, false, "database error");
+    if (!p_psDBRunQuery(config->dbh, query, whereClause, whereClause)) {
+        psError(PS_ERR_UNKNOWN, false, "database error");
+        psFree(whereClause);
         psFree(query);
         return false;
     }
+    psFree(whereClause);
     psFree(query);