            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:
   a) does this index current (SET ORDER TO) ,
   b) builds key for searching first record
   c) 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 "("

    IF poz1=0
     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:

FUNCTION FINDREC(expfnd) // expfnd - condition for searching
LOCAL rez:=.F.
   IF SPEEDFLT(expfnd) // If request is optimized,
    DBSEEK(stseek)     // search first record, partly satisfying
   ELSE                // condition, otherwise -
    GO TOP             // searching beginning of file
    stusl=".T."
   ENDIF
  rez=.F.
  DO WHILE .NOT. EOF().AND.&stusl
   IF &expfnd
    rez=.T // Suit record is found!
    EXIT
   ENDIF
 // Here possible insert anything for animation of process of searching
   SKIP
  ENDDO
RETURN rez

      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 <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 CLIPPER' - and now your requests ( SET FILTER 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.
</BODY>
</HTML>