The fastest way to filter DBF

[Ehab Samir Aziz]

I am extracting certain records from huge DBF (8000 Record) and it is so slow .

Code: Select all  Expand view

select 4
   4->(dbgotop())
   set filter to alltrim(me_cu_acct)=="2125447"



   4->(dbgotop())

DO WHILE !(4)->(EOF())


OleSetProperty( OleGetProperty( oSheet, "Cells", nLine, 1 ), "Value", (4)->ME_CU_name)
OleSetProperty( OleGetProperty( oSheet, "Cells", nLine, 2 ), "Value", (4)->me_mc_brn)
OleSetProperty( OleGetProperty( oSheet, "Cells", nLine, 3 ), "Value", (4)->me_mc_type)
OleSetProperty( OleGetProperty( oSheet, "Cells", nLine, 4 ), "Value", (4)->me_mc_modl)
OleSetProperty( OleGetProperty( oSheet, "Cells", nLine, 5 ), "Value", (4)->me_mc_serl)
OleSetProperty( OleGetProperty( oSheet, "Cells", nLine, 6 ), "Value", (4)->open_meter)
OleSetProperty( OleGetProperty( oSheet, "Cells", nLine, 7 ), "Value", (4)->cls_meter)
OleSetProperty( OleGetProperty( oSheet, "Cells", nLine, 8 ), "Value", (4)->prints)
OleSetProperty( OleGetProperty( oSheet, "Cells", nLine, 9 ), "Value", (4)->waste)
nLine:=nLine+1
(4)->(DBSKIP(1))


ENDDO



[James Bott]

Use scoping rather than a filter. This works with both NTXs and CDXs under Harbour and xHarbour.

First index it on me_cu_acct:

index on me_cu_acct to myindex

The set the scope:

cScope:="2125447" // or whatever

cScope:=padr(cScope),len(me_cu_acct))

select 4
4->(ordScope(0, cScope)) // set the top scope
4->(ordScope(1, cScope)) // set the bottom scope
4->(dbgotop())

[R.F.]

Scopes are great, try a scope and then a filter with the scoped database.

Or you can use the ADS LOCAL server, Filters are damn fast with ADS, even using SET FILTER and not need to use any additional indexes.

[Ehab Samir Aziz]

I am going to build a filter conditions . The previous message was for simplestic. What will be the solution for RDS and set scope with macro substiution . Thanks for help .

Code: Select all  Expand view

For i := 1 to 12

     IF Empty(a2_Vars[i])     && Don't process empty variables
         LOOP
     ENDIF

     DO CASE            && Create char value
         CASE VALTYPE(a2_vars[i]) == "D" .AND. i==11
                 cValue:= 'DTOS(' + a2_Fields[i] + ') >= "' + DTOS(a2_Vars[i]) + '"'
         CASE VALTYPE(a2_vars[i]) == "D" .AND. i==12
                 cValue := 'DTOS(' + a2_Fields[i] + ') <= "' + DTOS(a2_Vars[i]) + '"'
         CASE VALTYPE(a2_vars[i]) == "N"
             cValue := 'STR(' + a2_Fields[i] + ') == "' + STR(a2_vars[i]) + '"'       && or
         CASE VALTYPE(a2_vars[i]) == "C"
             cValue :="alltrim(upper("+ a2_Fields[i]+"))" + '=="' + alltrim(upper(a2_Vars[i])) + '"'
     ENDCASE
     IF Empty(cFilter)     && this is the first element
         cFilter :=  cValue
     ELSE
         cFilter += " .AND. " + cValue
     ENDIF
NEXT

set filter to &(cFilter)




[Enrico Maria Giordano]

Try using a conditional index:

INDEX ON yourkey TO temp FOR &(cFilter)

EMG

[Ehab Samir Aziz]

My key is varied depend on the conditions of the filter.

[James Bott]

Enrico was suggesting that you build a conditional index after the condition has been defined. This is useful if you are going to browse the resulting records as the browse will be much faster since the index is already built.

However, in your case you are only processing the recordset once so building the index will not speed it up. Either way the entire database has to be read once.

The only way to speed up a filter is to use a client/server system as Rene suggested.

James

[Ehab Samir Aziz]

This code does not work as Reneh suggested for ADS server.

Code: Select all  Expand view

a2_fields[1]:="mete->Me_CU_ACCt"
a2_fields[2]:="mete->Me_CU_name"
a2_fields[3]:="mete->Me_CT_CTNA"
a2_fields[4]:="mete->Me_mc_type"
a2_fields[5]:="mete->Me_mc_modl"
a2_fields[6]:="mete->Me_mc_serl"
a2_fields[7]:="mete->Me_mc_brn"
a2_fields[8]:="mete->Me_mc_mere"
a2_fields[9]:="mete->Me_mc_dep"
a2_fields[10]:="mete->Me_mc_loc"
a2_fields[11]:="mete->Me_date"
a2_fields[12]:="mete->Me_date"

a2_vars[1]:=oCombo1
a2_vars[2]:=oCombo2
a2_vars[3]:=oCombo3
a2_vars[4]:=oCombo4
a2_vars[5]:=oCombo5
a2_vars[6]:=oCombo6
a2_vars[7]:=oCombo7
a2_vars[8]:=oCombo8
a2_vars[9]:=oCombo9
a2_vars[10]:=oCombo10
a2_vars[11]:=ctod("01/06/2006")
a2_vars[12]:=ctod("30/06/2006")

For i := 1 to 12

     IF Empty(a2_Vars[i])     && Don't process empty variables
         LOOP
     ENDIF

     DO CASE            && Create char value
         CASE VALTYPE(a2_vars[i]) == "D" .AND. i==11
                 cValue:= 'DTOS(' + a2_Fields[i] + ') >= "' + DTOS(a2_Vars[i]) + '"'
         CASE VALTYPE(a2_vars[i]) == "D" .AND. i==12
                 cValue := 'DTOS(' + a2_Fields[i] + ') <= "' + DTOS(a2_Vars[i]) + '"'
         CASE VALTYPE(a2_vars[i]) == "N"
             cValue := 'STR(' + a2_Fields[i] + ') == "' + STR(a2_vars[i]) + '"'       && or
         CASE VALTYPE(a2_vars[i]) == "C"
             cValue :="upper("+ a2_Fields[i]+")" + '=="' + upper(a2_Vars[i]) + '"'
     ENDCASE
     IF Empty(cFilter)     && this is the first element
         cFilter :=  cValue
     ELSE
         cFilter += " .AND. " + cValue
     ENDIF
NEXT

use mete new
cReccount:= reccount()

mete->(dbgotop())
INDEX ON mete->me_mc_serl TO temp FOR &(cFilter)

bFilter = "{||("+cFilter+")}"
DBSETFILTER(&bFilter,cFilter)
mete->(dbgotop())




How ever how can I use the scope with variable conditions like the contents of the above cFilter.

[Enrico Maria Giordano]

How ever how can I use the scope with variable conditions like the contents of the above cFilter.

You generally can't. Specifically you can take advantage of the available indexes by manually selecting the one that scopes out most of your records.

EMG

[Ehab Samir Aziz]

Oka I solved the last thread. BUT I created the index in a variable manner I faced create error .

Code: Select all  Expand view

For i := 1 to 12
     IF Empty(a2_Vars[i]) .or. (i==12)     && Don't process empty variables
         LOOP
     ENDIF

     DO CASE            && Create char value
         CASE VALTYPE(a2_vars[i]) == "C"
             cValue :="upper("+ a2_Fields[i]+")"
         CASE VALTYPE(a2_vars[i]) == "N"
             cValue := 'STR(' + a2_Fields[i] + ')'
         CASE VALTYPE(a2_vars[i]) == "D" .AND. (i==11)
                 cValue:= 'DTOS(' + a2_Fields[i] + ')'
     ENDCASE
     IF Empty(cFilteri)     && this is the first element
         cFilteri :=  cValue
     ELSE
         cFilteri += " .AND. " + cValue
     ENDIF
NEXT

cFilteri :="("+cFilteri+")"
use mete new
mete->(dbgotop())
INDEX ON &(cFilteri) TO temp unique
mete->(dbgotop())



[Ehab Samir Aziz]

Oka I am searching for fastenning my filter . It is not fast with RDD and that code :

Code: Select all  Expand view


use mete new
cReccount:= reccount()
mete->(dbgotop())
INDEX ON mete->me_mc_serl TO temp FOR &(cFilter) unique
mete->(dbgotop())

bFilter = "{||("+cFilter+")}"
cFilter = "("+cFilter+")"

DBSETFILTER(&bFilter,&cFilter)

mete->(dbgotop())





[Enrico Maria Giordano]

Try removing this code. It is redundant:

Code: Select all  Expand view

bFilter = "{||("+cFilter+")}"
cFilter = "("+cFilter+")"

DBSETFILTER(&bFilter,&cFilter)

mete->(dbgotop())

EMG

[Ehab Samir Aziz]

My last thread was about it is not fast with RDD. Also I need to create variable index conditions according to the input conditions.

[Enrico Maria Giordano]

You can remove

Code: Select all  Expand view

bFilter = "{||("+cFilter+")}"
cFilter = "("+cFilter+")"

DBSETFILTER(&bFilter,&cFilter)

mete->(dbgotop())

as it is replaced by

Code: Select all  Expand view

INDEX ON mete->me_mc_serl TO temp FOR &(cFilter) unique

EMG

[Badara Thiam]

I am extracting certain records from huge DBF (8000 Record) and it is so slow .

You should not use DBGOTOP() after SET FILTER
because this FILTER the database before ALL.

Use generic SEEK to find directly the first available record
and then you can DBSKIP().

With Clipper 5.3b, filters take 2 or 3 seconds before
to see the list, in a database with 8000 records
and on a pc celeron 500 with 64 Mo.

Test my sample below please, i am interested
to know the speed of your procedure.

Regards,

Code: Select all  Expand view

select 4            && indexed with a "char" key
   4->(dbgotop())
   set filter to alltrim(me_cu_acct)=="2125447"



*   4->(dbgotop())

Sysrefresh()
DBSEEK(SPACE(LEN( First_Field_In_The_Index_Key ) - 1) + "!", .T.)
Sysrefresh()

DO WHILE !(4)->(EOF())


OleSetProperty( OleGetProperty( oSheet, "Cells", nLine, 1 ), "Value", (4)->ME_CU_name)
OleSetProperty( OleGetProperty( oSheet, "Cells", nLine, 2 ), "Value", (4)->me_mc_brn)
OleSetProperty( OleGetProperty( oSheet, "Cells", nLine, 3 ), "Value", (4)->me_mc_type)
OleSetProperty( OleGetProperty( oSheet, "Cells", nLine, 4 ), "Value", (4)->me_mc_modl)
OleSetProperty( OleGetProperty( oSheet, "Cells", nLine, 5 ), "Value", (4)->me_mc_serl)
OleSetProperty( OleGetProperty( oSheet, "Cells", nLine, 6 ), "Value", (4)->open_meter)
OleSetProperty( OleGetProperty( oSheet, "Cells", nLine, 7 ), "Value", (4)->cls_meter)
OleSetProperty( OleGetProperty( oSheet, "Cells", nLine, 8 ), "Value", (4)->prints)
OleSetProperty( OleGetProperty( oSheet, "Cells", nLine, 9 ), "Value", (4)->waste)
nLine:=nLine+1

  Sysrefresh()
  DBSKIP(1)
  Sysrefresh()

ENDDO