Changeset 71


Ignore:
Timestamp:
30.10.2008 23:11:13 (11 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:
1 edited

Legend:

Unmodified
Added
Removed
  • SH_SHM/trunk/source/sqliface.c

    r46 r71  
    33 *      ========== 
    44 * 
    5  * $Revision: 31 $, $Date: 2008-03-03 20:47:24 +0100 (Mo, 03 MÀr 2008) $ 
     5 * $Revision: 56 $, $Date: 2008-10-28 16:01:29 +0100 (Di, 28 Okt 2008) $ 
    66 * 
    77 * Interface to SQL databases 
     
    188188        char     tmpfile[cBcFileLth+1];            /* scratch file, not used */ 
    189189        NTIME    sntime, entime;                   /* numeric read times */ 
     190        NTIME    soffset, eoffset;                 /* limitation of search range */ 
    190191        int      datea, dateb;                     /* dates of seed entry */ 
    191192        double   timea, timeb;                     /* times of entry */ 
    192193        int      rd1date, rd2date;                 /* read date as integer */ 
    193194        double   rd1time, rd2time;                 /* read time as double */ 
     195        int      rd1off, rd2off;                   /* date part of soffset, eoffset */ 
    194196 
    195197        /* executable code */ 
     
    207209        /* call to db if new start time given */ 
    208210        if  (strcmp(start,last_start) != 0 || readlth != last_readlth)  { 
     211                tc_nadd( &sntime, -86400*30*3, &soffset, status ); 
     212                if  (SySevere(status))  return; 
     213                tc_nadd( &entime, 86400*30*3, &eoffset, status ); 
     214                if  (SySevere(status))  return; 
    209215                rd1date = sntime.year*10000 + sntime.month*100 + sntime.day; 
    210216                rd1time = (double)sntime.hour*10000.0 + (double)sntime.min*100.0 
     
    213219                rd2time = (double)entime.hour*10000.0 + (double)entime.min*100.0 
    214220                        + (double)entime.sec + (double)entime.ms/1000.0; 
     221                rd1off = soffset.year*10000 + soffset.month*100 + soffset.day; 
     222                rd2off = eoffset.year*10000 + eoffset.month*100 + eoffset.day; 
    215223                sprintf( shellcmd, 
    216                         "\\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", 
     224                        "\\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", 
    217225                        sqlv_sfdb_tmp, GpGetString(cGpS_sfdb_command), 
    218                         GpGetString(cGpS_sfdb_exec_qual), TABLE_ELEMENTS, rd1date, rd1date, 
    219                         rd1time, rd2date, rd2date, rd2time, GpGetString(cGpS_sfdb_trailer), 
    220                         sqlv_sfdb_tmp ); 
     226                        GpGetString(cGpS_sfdb_exec_qual), TABLE_ELEMENTS, rd2off, rd1off, 
     227                        rd1date, rd1date, rd1time, rd2date, rd2date, rd2time, 
     228                        GpGetString(cGpS_sfdb_trailer), sqlv_sfdb_tmp ); 
    221229                if  (GpGetInt(cGpI_debug_level) > 2) 
    222230                        printf( "SH-dbg3: executing %s\nSH-dbg3: command line length %d\n", 
Note: See TracChangeset for help on using the changeset viewer.