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.