Optimization of queries by means of index files

It is known, that some operations, such as LOCATE FOR, SUM FOR, moving through the filtered table are quite slow and this may be critical if the database is big enough, especially if it is located in a file server. Using of indexes can significantly accelerate this process, and, surely, if you know in advance the LOCATE or FILTER expression, you will try to do this in your program.

But what if the expression is not known beforehand, if it is determined, for example, by the end user ? It is a case, for which the proposed method is intended.

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

If it find one, it:

See at the code below:

        Parameter expflt - an expression, which execution we should
        optimize, for instance, "DNAKL=CTOD("01/01/96").AND.SUMMA>1000000... "
    LOCAL rez  := .F.,;  // a variable to keep a result of analysis
          iord := 0,  ;  // index counter
          ielem, elemf, ellen, elemzn, poz, poz1, poz2, poz3, exptmp, expind

    PUBLIC stseek,;      // a key to search the first record
           stusl         // an expression for DO WHILE cycle

       STORE "" TO stseek, stusl

       // Beginning of main cycle - scan and analysis all opened indexes  
       DO WHILE !rez
          iord++         // Increment index counter
                         // take a key expression
          expind := Upper( Ordkey( iord ) )

          IF Empty( expind )   // Exit, if there no more indexes
          ELSE                 // otherwise - we begin analysis of a key expression

          We look for expression items, divided by the sign "+".
          For example, in the expression "DTOS(DNAKL) + NNAKL + STR(CENA,11,2)"
          we have three items:  DTOS(DNAKL), NNAKL and STR(CENA,11,2)

          DO WHILE !Empty( expind )

            // Look for the next item
            poz := At( "+",expind )
            IF poz != 0
               ielem := Left( expind,poz-1 )
               poz ++
               expind := Substr( expind,poz )
              ielem := expind
              expind := ""

            // Then we check, is the item a function ( by presence of "("
            // or it is only a field name
            poz1 := At( "(", ielem )

            IF poz1 == 0
               elemf := ielem // it is a fieldname
               // if we found opening bracket "(", we cut
               // the field name from the item, supposing that it begins right after
               // this parentheses and ends with comma "," or closing bracket ")"
               elemf := Substr( ielem,poz1+1 )
               poz2  := At( ",",elemf )

               IF poz2 == 0
                 poz2 := At( ")", elemf )
               IF poz2 != 0
                  elemf := Left( elemf,poz2-1 )

            ellen := Len( elemf )   // Get length of a field name

            // Now search this fieldname in 'expflt'
            poz2 := At( elemf, Upper(expflt) )

            IF poz2 > 0 .AND. Substr( expflt,poz2+ellen,1 ) == "="
               // If it is found, and it is followed by the "="
               IF At( elemf, Upper( Substr( expflt,poz2+ellen+1 ) ) ) == 0
                   and this field name is not met in the expression anymore
                   ( it is needed to exclude expressions with .OR.
                   on this field ) to be sure that current key expression allows
                   to optimize a performing of an expression.
                  rez := .T.
                   Now continue an analysis to build stseek and stusl.
                   For this purpose we extract the required value of a field 
                   from an expression.
                   We consider that this value must be in "expflt" immediately
                   after the "=", and will be completed by a newline
                   or a dot "." ( if there are .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 )

                  elemzn := Substr( elemzn, 1, Iif(poz2==0,999,poz2-1) )
                  // Now build 'stusl'
                  stusl += Iif( Empty( stusl ), "", ".AND." ) + elemf + "=" + elemzn
                  // and 'stseek'. For this we substitute found line with value
                  // fields (elemzn) in key expression item instead of a field name,
                  // compute a tin expression and add to stseek!
                  exptmp := Stuff( ielem, poz1+1, ellen, elemzn )
                  stseek := stseek + &exptmp
            IF !( elemf $ stusl )
               // If next element not suitable for optimization,
               // stop an analysis of current key expression.

      IF rez
         // If suitable index is found, set it as current
         Ordsetfocus( iord )

    RETURN rez

Now we can optimize, for example the LOCATE FOR command:

    FUNCTION FINDREC( expfnd ) // expfnd - condition for searching
    LOCAL rez := .F.

       IF Speedflt( expfnd )   // If the request is optimized,
          Dbseek( stseek )     // search first record, partly satisfying
       ELSE                    // the condition, otherwise -
          GO TOP               // go to the beginning of a file
          stusl := ".T."
       rez := .F.
       DO WHILE !Eof() .AND. &stusl
          IF &expfnd
             rez := .T // Suit record is found!
          // Here you can insert something to animate the process of searching
    RETURN rez

It is easy now to write similar functions for SUM... FOR..., COUNT FOR..., etc.

You may redefine corresponding Clipper commands - and they will work much more quickly in case of the expression is optimizable, for example:

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

To optimize the movement through a filtered table you may write a function, similar to the Findrec(), which will place numbers of filtered records in the special array for following use and redefine commands for moving through the table - such as GO TOP, GO BOTTOM, SKIP. If you use TBrowse, you should define their own codeblocks for moving down the 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 was set by the Speedflt(). If you want records were located in order, determined by that index, which was current before filtration, it is possible to include in abovementioned array a line

KEY + STR(record number),

where KEY - a value of key expression of initial index for given record ( &(Ordkey(iord)) ). After terminating the filtrations just sort a received array.

[Download sample][Return to the clipper page]