Changeset 71

Timestamp:

10/30/2008 11:11:13 PM (15 years ago)

Author:

marcus

Message:

r56 | svn | 2008-10-28 16:01:29 +0100 (Di, 28 Okt 2008) | 1 line

fix slow queries: limit search range in sql statement

File:

• SH_SHM/trunk/source/sqliface.c (modified) (4 diffs)

SH_SHM/trunk/source/sqliface.c

@@ -3 +3 @@
  *      ==========
  *
- * $Revision: 31 $, $Date: 2008-03-03 20:47:24 +0100 (Mo, 03 MÀr 2008) $
+ * $Revision: 56 $, $Date: 2008-10-28 16:01:29 +0100 (Di, 28 Okt 2008) $
  *
  * Interface to SQL databases
@@ -188 +188 @@
         char     tmpfile[cBcFileLth+1];            /* scratch file, not used */
         NTIME    sntime, entime;                   /* numeric read times */
+        NTIME    soffset, eoffset;                 /* limitation of search range */
         int      datea, dateb;                     /* dates of seed entry */
         double   timea, timeb;                     /* times of entry */
         int      rd1date, rd2date;                 /* read date as integer */
         double   rd1time, rd2time;                 /* read time as double */
+        int      rd1off, rd2off;                   /* date part of soffset, eoffset */
 
         /* executable code */
@@ -207 +209 @@
         /* call to db if new start time given */
         if  (strcmp(start,last_start) != 0 || readlth != last_readlth)  {
+                tc_nadd( &sntime, -86400*30*3, &soffset, status );
+                if  (SySevere(status))  return;
+                tc_nadd( &entime, 86400*30*3, &eoffset, status );
+                if  (SySevere(status))  return;
                 rd1date = sntime.year*10000 + sntime.month*100 + sntime.day;
                 rd1time = (double)sntime.hour*10000.0 + (double)sntime.min*100.0
@@ -213 +219 @@
                 rd2time = (double)entime.hour*10000.0 + (double)entime.min*100.0
                         + (double)entime.sec + (double)entime.ms/1000.0;
+                rd1off = soffset.year*10000 + soffset.month*100 + soffset.day;
+                rd2off = eoffset.year*10000 + eoffset.month*100 + eoffset.day;
                 sprintf( shellcmd,
-                        "\\rm %s; %s %s \"select %s from sftab where (((edate > %d) OR (edate = %d AND etime >= %10.3lf)) AND ((sdate < %d) OR (sdate = %d AND stime <= %10.3lf))) ORDER BY sdate,stime\" %s >%s",
+                        "\\rm %s; %s %s \"select %s from sftab where edate < %d and sdate > %d and ((edate > %d) OR (edate = %d AND etime >= %10.3lf)) AND ((sdate < %d) OR (sdate = %d AND stime <= %10.3lf)) ORDER BY sdate,stime\" %s >%s",
                         sqlv_sfdb_tmp, GpGetString(cGpS_sfdb_command),
-                        GpGetString(cGpS_sfdb_exec_qual), TABLE_ELEMENTS, rd1date, rd1date,
-                        rd1time, rd2date, rd2date, rd2time, GpGetString(cGpS_sfdb_trailer),
-                        sqlv_sfdb_tmp );
+                        GpGetString(cGpS_sfdb_exec_qual), TABLE_ELEMENTS, rd2off, rd1off,
+                        rd1date, rd1date, rd1time, rd2date, rd2date, rd2time,
+                        GpGetString(cGpS_sfdb_trailer), sqlv_sfdb_tmp );
                 if  (GpGetInt(cGpI_debug_level) > 2)
                         printf( "SH-dbg3: executing %s\nSH-dbg3: command line length %d\n",