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:
- set this index current (SET ORDER TO) ,
- calculates a key to search the first record
- calculates an expression for DO WHILE cycle .
See at the code below:
FUNCTION SPEEDFLT( expflt ) /* 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 EXIT 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 ) ELSE ielem := expind expind := "" ENDIF // 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 ELSE // 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 ) ENDIF IF poz2 != 0 elemf := Left( elemf,poz2-1 ) ENDIF ENDIF 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 ) ENDIF 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 ENDIF ENDIF IF !( 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, set it as current Ordsetfocus( iord ) ENDIF 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." ENDIF rez := .F. DO WHILE !Eof() .AND. &stusl IF &expfnd rez := .T // Suit record is found! EXIT ENDIF // Here you can insert something to animate the process of searching SKIP ENDDO 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.