It confirmed my idea, but is it true ... ??
I use Xbrowse and the aBarget a Lot, and therefore my question :
The best way to filter data :
Folowing many discussies in the forum, i know that Mr. Rao is in favor for a optimised set filter to ....
I think that with my code (see below) I do just that and yes, It is quite fast.
But I have still a unansered question :
Indexes build normaly optimised and as a sample here a tag on BRANDID and also FABRIKAN (no index on NAAM)
Folowing typical filter :
"PRO" $ UPPER( BRANDID ) .AND. "RANGER" $ UPPER( NAAM ) .AND. "ABOUT" $ UPPER( FABRIKAN )
1. A Filter is working from left to right, processing : BrandID, Naam, Fabrikan
Since there is a TAG on BRANDID, the filter is FAST since it has the 1 tag, then keeping the resultdata and process them with the Naam FILTER as a Loop, since there is NO tag for the 2de part of the filter etc ...
If I change the incomming filter string in a order that first ALL indexed fields are beeing processed and then the NOT having a TAG fields
is this always better ?
For the argument, let say that the field BRANDID has no tag, will the current filter be slow because he has not found a TAG on brandid and need to process ALL data from top to bottem.
2. This is correct thinking or not ?
ChatGPT also thinks that arranging the filter with field that have tag as first filters are faster.
ChatGpt second option :
rearrange the filter string also that field with a tag are in front of the Filterstring
then process like this :
1 First field : ORDSCOPE the selection and put the result in a Array (1 filter is done)
2 field : Ordscope (the Array) and generate a newArray (2 filters are done)
3 field : Process the NewArray itself inside a loop excluding the data not needed (because of no index)
Give the result data back to xBrowse
Before I try to code one of these options, I would love to hear the ideas of this from Forum and FWTeam.
Thank
Code: Select all | Expand
FUNCTION Marc_SETFILTER( oBrw )
LOCAL cFilter := ""
LOCAL n, oCol, uVal, cType, cVal1, cVal2, nGevonden
LOCAL nZoekKnop, cExprt
LOCAL cAlias:= alias()
FOR n := 1 TO Len( oBrw:aCols )
oCol := oBrw:aCols[ n ]
IF ! Empty( uVal := oCol:uBarGetVal )
IF !Empty( cFilter )
cFilter += " .AND. "
ENDIF
cType := ValType( uVal )
if cType = "C"
nGevonden = at("++",uVal) // I do this for multiple search in 1 go
endif
if cType = "C"
nZoekKnop = at("²",uVal) // To force a extra key to activated the search in browse
endif
DO CASE
CASE cType == 'C' .and. nZoekKnop > 0
uVal := Upper( AllTrim( uVal ) )
cVal1 = substr(uVal,1,nZoekknop-2)
cFilter += '"' + cVal1 + '" $ UPPER( ' + oCol:CExpr + " )"
CASE cType == 'C' .and. nGevonden > 0
uVal := Upper( AllTrim( uVal ) )
cVal1 = substr(uVal,1,nGevonden-1)
cVal2 = substr(uVal,nGevonden+2)
cFilter += '"' + cVal1 + '" $ UPPER( ' + oCol:CExpr + " )"
cFilter += " .AND. "
cFilter += '"' + cVal2 + '" $ UPPER( ' + oCol:CExpr + " )"
CASE cType == 'C'
uVal := Upper( AllTrim( uVal ) )
cFilter += '"' + uVal + '" $ UPPER( ' + oCol:CExpr + " )"
CASE cType == 'D'
cFilter += oCol:cExpr + " = " + ( uVal )
OTHERWISE
cFilter += oCol:cExpr + " == " + cValToChar( uVal )
ENDCASE
ENDIF
NEXT
IF Empty( cFilter )
IF ! Empty( dbFilter() )
dbClearFilter()
oBrw:Refresh()
ENDIF
ELSE
IF !( dbFilter() == cFilter )
oClp:SetText(cFilter) // For personel creating of Indexes to use more times
if !empty(cAlias)
SET FILTER TO &cFilter
oClp:SetText(cFilter)
GO TOP
else
msginfo("Error.... Geen Alias beschikbaar")
endif
oBrw:Refresh()
ENDIF
ENDIF
oBrw:maketotals()
oBrw:SetFocus()
RETURN NIL
