The fastest way to filter DBF

Ehab Samir Aziz · Post by **Ehab Samir Aziz** » Tue Aug 01, 2006 9:30 pm

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

 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 · Post by **James Bott** » Tue Aug 01, 2006 9:49 pm

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. · Post by **R.F.** » Wed Aug 02, 2006 1:54 am

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 · Post by **Ehab Samir Aziz** » Wed Aug 02, 2006 4:34 pm

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

 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)

Wed Aug 02, 2006 4:43 pm

Try using a conditional index:

INDEX ON yourkey TO temp FOR &(cFilter)

EMG

Ehab Samir Aziz · Post by **Ehab Samir Aziz** » Wed Aug 02, 2006 4:57 pm

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

James Bott · Post by **James Bott** » Wed Aug 02, 2006 5:16 pm

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 · Post by **Ehab Samir Aziz** » Fri Aug 04, 2006 12:35 pm

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

Code: Select all

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.

Fri Aug 04, 2006 12:46 pm

Ehab Samir Aziz wrote: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 · Post by **Ehab Samir Aziz** » Fri Aug 04, 2006 1:02 pm

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

Code: Select all

 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 · Post by **Ehab Samir Aziz** » Fri Aug 04, 2006 5:07 pm

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

Code: Select all


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())

Fri Aug 04, 2006 7:03 pm

Try removing this code. It is redundant:

Code: Select all

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

DBSETFILTER(&bFilter,&cFilter)

mete->(dbgotop())

EMG

Ehab Samir Aziz · Post by **Ehab Samir Aziz** » Sat Aug 05, 2006 11:35 am

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

Sat Aug 05, 2006 12:02 pm

You can remove

Code: Select all

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

DBSETFILTER(&bFilter,&cFilter) 

mete->(dbgotop())

as it is replaced by

Code: Select all

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

EMG

Badara Thiam · Post by **Badara Thiam** » Sun Aug 06, 2006 3:21 pm

Ehab Samir Aziz wrote: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

 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

FiveTech Software tech support forums

The fastest way to filter DBF

The fastest way to filter DBF

Re: The fastest way to filter DBF