Optimization of queries by means of index files

    In the report considered method, allowing vastly accelerate a performing such
operations, as a searching, filtration etc.

    The Method realized by means of functions, written on clean Clipper'�, without
using additional libraries. Text to functions is provided in the report.

    Increasing of velocity of performing the queries - an old problem of Clipperheads.
If database sufficiently great, the waiting the results of operations (with use
SET FILTER TO..., LOCATE FOR..., SUM... FOR... etc.) can be delayed for a
long time, but if additionally on the table beside you not PENTIUM, or in
case of network tense traffic - better go on the dinner.

    But if limit a kit of possible queries, having assigned them place in the program
and using where is necessary SEEK's, we, certainly, powerfully win in velocities,
but in the same way will powerfully lose in flexibility, having limited hereunder
possibility of program.

    This problem to a considerable extent speech in such products, as SIX Driver
company SuccessWare and COMIX company LoadStone Inc.

    Use SET SCOPE instead of SET FILTER vastly accelerates a process of sample.
Built-in in these products MACH QUERY OPTIMIZER allows repeatedly to raise a
velocity of performing the commands SET FILTER TO, LOCATE FOR , SUM... FOR etc.
(in that event, naturally, if openned indexes allow this).

    Is Necessary say that in composition CLIPPER 5.3 enters RDD DBFCDX from LoadStone
Inc., which is kept MACH QUERY OPTIMIZER.

    But not everyone have these, certainly, remarkable products.
Besides, MACH QUERY OPTIMIZER don't work with NTX indexes.

    It is Necessary to say that not always suitable restrictions, superimposed on
expressions, subjecting optimization.

    For instance, we have open an index with key expression

       DTOS(DNAKL)+NNAKL

    Natural form of expressions of sample on a date is, for instance, such:

       DNAKL=CTOD("01/01/96")

    But no! Six Driver does not be able to include MACH QUERY OPTIMIZER for optimization
of performing such request! It needed such expression:

       DTOS(DNAKL)=DTOS(CTOD("01/01/96"))

    Similar situation and with such often used in key expressions by functions,
as STR() and SUBSTR().

    I propose another method:

    Program scans all openned indexes and by the analysis of key expressions tries
to find an index, allowing optimize a performing a given request.

    In the event of good luck it:

• does this index current (SET ORDER TO) ,
• builds key for searching first record
• and expression for DO WHILE... .

FUNCTION SPEEDFLT(expflt)
/*
   Parameter expflt - a source expression of request, which execution should
   optimize, for instance, "DNAKL=CTOD("01/01/96").AND.SUMMA>1000000... "
*/
LOCAL rez:=.F.,; // Will Here be brought result of analysis on possibility
         // optimization (.T or .F.)
       iord:=0,; // Openning index Counter
   ielem,elemf,ellen,elemzn,poz,poz1,poz2,poz3,exptmp,expind
PUBLIC stseek,; // Will Here be formed key for command SEEK stusl
         // but here - condition of completion of sample
  STORE "" TO stseek,stusl
     // Beginning of main cycle - scan and analysis all openned indexes, while
     // will not be found satisfying
  DO WHILE .NOT.rez
     iord++ // Increment openning index counter
         // Hereinafter take a key expression
#ifdef RDD_NTX // if they are used by NTX indexes
   expind=UPPER(INDEXKEY(iord))
#else // if CDX, MDX and others.
   expind=UPPER(ORDKEY(iord))
#endif

   IF EMPTY(expind) // If more no openned indexes - to finish scanning,
    EXIT
   ELSE // otherwise - to begin analysis of key expression
/*
       In the following cycle are chosen one for other key expression elements,
       basing in expind, until will be reached its end.
       Under elements are understood divided by the sign "+" names of floors
       of database or expressions from floors, for instance, in the
       expression "DTOS(DNAKL) + NNAKL + STR(CENA,11,2)" we have three elements:
       DTOS(DNAKL), NNAKL and STR(CENA,11,2)
*/
    DO WHILE .NOT.EMPTY (expind)
     // To take next key expression element (ielem)
     // and select from it name of field (elemf)

     poz=AT("+",expind)

     IF poz<>0
       // If be ahead of "+", the next element - from beginning of line -
       // before "+", then slice its from line expind
      ielem=SUBSTR(expind,1,poz-1)
      poz=poz+1
      expind=SUBSTR(expind,poz)
     ELSE
       // But if "+" ahead of no - signifies we stay before the last element.
       // Take its and clean a line that will serve a sign for the completion of cycle.
      ielem=expind
      expind=""
     ENDIF

     // Now check, be in chosen element a function or it
     // consists only from name of field
     poz1=AT("(",ielem) // Presence of functions define on presence "("
      elemf=ielem // If its no, the name of field == expression element
     ELSE
       // But if found openning bracket "(", we cut
       // name of field from element, supposing that it begins right after
       // this parentheses and ends comma "," or closing bracket ")"
      elemf=SUBSTR(ielem,poz1+1) poz2=AT(",",elemf)
      IF poz2=0
       poz2=AT(")",elemf)
      ENDIF
      IF poz2<>0
       elemf=SUBSTR(elemf,1,poz2-1)
      ENDIF
     ENDIF

     ellen=LEN(elemf) // Get length of name of field
     // Now search name of field in 'expflt'
     poz2=AT(elemf,UPPER(expflt))

     IF poz2>0.AND.SUBSTR(expflt,poz2+ellen,1)="="
     // If have found, and for he follow sign "=",
      IF AT(elemf,UPPER(SUBSTR(expflt,poz2+ellen+1)))=0
       /*
       and second once this name of field in the expression is not met
       ( this needed to exclude expressions with relations .OR.
       on this field) that consider that current key expression allows
       to optimize a performing a request.
       */
       rez=.T.
       /*
       Now continue an analysis to form stseek and stusl.
       For this select from expflt required value of field.
       Herewith consider that sought value situated in the line immediately
       for the sign "=", sign of completion its we suppose a newline
       or "." ( if trace goes .AND or .OR., )
       */
       elemzn=SUBSTR(expflt,poz2+ellen+1)
       poz2=AT(".",elemzn)
       IF poz2<>0.AND.ISDIGIT(SUBSTR(elemzn,poz2+1))
        poz3=AT(".",SUBSTR(elemzn,poz2+1))
        poz2=poz2+IIF(poz3=0,999,poz3-1)
       ENDIF

       // Definable borders, select line - value of field (elemzn)
       elemzn=SUBSTR(elemzn,1,IIF(poz2=0,999,poz2-1))
       // Now form 'stusl'
       stusl=stusl+IIF(EMPTY(stusl),"",".AND.")+elemf+"="+elemzn
       // and 'stseek'. For this substitute found line with value
       // fields (elemzn) in key expression element instead of name of field,
       // compute a tin expression and add to stseek!
       exptmp=STUFF(ielem,poz1+1,ellen,elemzn)
       stseek=stseek+&exptmp
      ENDIF
     ENDIF
     IF .NOT. (elemf $ stusl)
     // If next element not suitable for optimization,
     // stop an analysis of current key expression.
      EXIT
     ENDIF
    ENDDO
   ENDIF
  ENDDO

  IF rez
   // If suitable index is found, install its current
#ifdef RDD_NTX
   SET ORDER TO iord
#else
   ORDSETFOCUS(iord)
#endif
  ENDIF
   // Function returns a result of its functioning (.T or .F.)
RETURN rez

    To not to overfill a report codes, I has brought this function in a little
shortenned ( but wholly runnable) type. As you, surely, have noticed, in
this variant is not optimized performing the requests with using the signs
'<', '>'. You themselves without the particular labour will be able to
modify SPEEDFLT function() to increase its possibility.

    Now we can write for the searching (LOCATE FOR...) such, for instance, a forgive
function:

    Similar functions possible to write for SUM... FOR..., COUNT FOR... and etc.

    But if redefy correspond commands of CLIPPER, it will possible use them as
earlier, but now they will work much quicker (if expression of request subjects
optimization).

    Command LOCATE, for instance, possible redefy as follows:

    #command LOCATE [FOR ] ;
       => findrec( <{for}> )

As to filtrations, here suitable will write a function, similar above brought by
FINDREC(), which will place numbers of filtered records in the special array for
following use. Needed will also redefy commands moving down to the base - such as
GO TOP, GO BOTTOM, SKIP. If you use TBrowse for the output tables on the screen, you
should define their own blocks of code for moving down to filtered table.

   It is Necessary to note that after filtration records will be located not in the
initial order, but in accordance with that index, which has defined a function
of optimization SPEEDFLT(). MACH QUERY OPTIMIZER does no changes order. In my
opinion, after filtrations it builds a new temporary index, probably, with use
RYO subscripting.

    As far as we do not use SIX Driver and so have no possibilities to build RYO indexes,
I suggest other way.

    If you want records were located in order, determined by that index, which was current
before filtration, in abovementioned array possible to bring a line of type

       KEY + STR(record number),

   where KEY - a value of key expression of initial index for given record ( &(ORDKEY(iord)) ).
After terminating the filtrations sort a received array and select from it again record number.

    Summary.

    Using described in the report a technology, you include to your program the function
SPEEDFLT() and functions of type FINDREC(), re-define several commands of CLIPPER
- and now your requests ( SET FILTTER TO, LOCATE FOR, SUM... FOR etc.) will become
be executed much quicker (in that events, certainly, when open indexes, allowing
do this).

Functions carry an universal nature, so their does not need to change for each new
database, each new task.

They work and with CLIPPER 5.* and with CLIPPER 87 (after small changing of syntax).

They work with different RDD - with NDX, NTX, CDX, MDX and others.