FiveTech Support Forums

FiveWin / Harbour / xBase community
Board indexAll discussionsLogin (GitHub)
Board index FiveWin for Harbour/xHarbour The fastest way to filter DBF
Ehab Samir Aziz
Posts: 334
Joined: Fri Oct 14, 2005 01:54 PM
The fastest way to filter DBF
Posted: Tue Aug 01, 2006 09:30 PM
I am extracting certain records from huge DBF (8000 Record) and it is so slow .
Code: Select all Collapse
 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
Posts: 4840
Joined: Fri Nov 18, 2005 04:52 PM
The fastest way to filter DBF
Posted: Tue Aug 01, 2006 09: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())

FWH 18.05/xHarbour 1.2.3/BCC7/Windows 10
R.F.
Posts: 840
Joined: Thu Oct 13, 2005 07:05 PM
The fastest way to filter DBF
Posted: Wed Aug 02, 2006 01: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.

Saludos

R.F.
Ehab Samir Aziz
Posts: 334
Joined: Fri Oct 14, 2005 01:54 PM
The fastest way to filter DBF
Posted: Wed Aug 02, 2006 04: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 Collapse
 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
Posts: 9020
Joined: Thu Oct 06, 2005 08:17 PM
The fastest way to filter DBF
Posted: Wed Aug 02, 2006 04:43 PM

Try using a conditional index:

INDEX ON yourkey TO temp FOR &(cFilter)

EMG

Enrico Maria Giordano

e.m.giordano@emagsoftware.it
http://www.emagsoftware.it
http://www.emagsoftware.it/emgmusic
http://www.emagsoftware.it/spectrum
http://www.emagsoftware.it/tbosg
Ehab Samir Aziz
Posts: 334
Joined: Fri Oct 14, 2005 01:54 PM
The fastest way to filter DBF
Posted: Wed Aug 02, 2006 04:57 PM

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

James Bott
Posts: 4840
Joined: Fri Nov 18, 2005 04:52 PM
The fastest way to filter DBF
Posted: Wed Aug 02, 2006 05: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

FWH 18.05/xHarbour 1.2.3/BCC7/Windows 10
Ehab Samir Aziz
Posts: 334
Joined: Fri Oct 14, 2005 01:54 PM
The fastest way to filter DBF
Posted: Fri Aug 04, 2006 12:35 PM
This code does not work as Reneh suggested for ADS server.
Code: Select all Collapse
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
Posts: 9020
Joined: Thu Oct 06, 2005 08:17 PM
The fastest way to filter DBF
Posted: 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
Enrico Maria Giordano

e.m.giordano@emagsoftware.it
http://www.emagsoftware.it
http://www.emagsoftware.it/emgmusic
http://www.emagsoftware.it/spectrum
http://www.emagsoftware.it/tbosg
Ehab Samir Aziz
Posts: 334
Joined: Fri Oct 14, 2005 01:54 PM
The fastest way to filter DBF
Posted: Fri Aug 04, 2006 01:02 PM
Oka I solved the last thread. BUT I created the index in a variable manner I faced create error .
Code: Select all Collapse
 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
Posts: 334
Joined: Fri Oct 14, 2005 01:54 PM
The fastest way to filter DBF
Posted: Fri Aug 04, 2006 05:07 PM
Oka I am searching for fastenning my filter . It is not fast with RDD and that code :
Code: Select all Collapse
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
Posts: 9020
Joined: Thu Oct 06, 2005 08:17 PM
The fastest way to filter DBF
Posted: Fri Aug 04, 2006 07:03 PM
Try removing this code. It is redundant:

Code: Select all Collapse
bFilter = "{||("+cFilter+")}"
cFilter = "("+cFilter+")"

DBSETFILTER(&bFilter,&cFilter)

mete->(dbgotop())


EMG
Enrico Maria Giordano

e.m.giordano@emagsoftware.it
http://www.emagsoftware.it
http://www.emagsoftware.it/emgmusic
http://www.emagsoftware.it/spectrum
http://www.emagsoftware.it/tbosg
Ehab Samir Aziz
Posts: 334
Joined: Fri Oct 14, 2005 01:54 PM
The fastest way to filter DBF
Posted: 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.

Enrico Maria Giordano
Posts: 9020
Joined: Thu Oct 06, 2005 08:17 PM
The fastest way to filter DBF
Posted: Sat Aug 05, 2006 12:02 PM
You can remove

Code: Select all Collapse
bFilter = "{||("+cFilter+")}" 
cFilter = "("+cFilter+")" 

DBSETFILTER(&bFilter,&cFilter) 

mete->(dbgotop())


as it is replaced by

Code: Select all Collapse
INDEX ON mete->me_mc_serl TO temp FOR &(cFilter) unique


EMG
Enrico Maria Giordano

e.m.giordano@emagsoftware.it
http://www.emagsoftware.it
http://www.emagsoftware.it/emgmusic
http://www.emagsoftware.it/spectrum
http://www.emagsoftware.it/tbosg
Badara Thiam
Posts: 160
Joined: Tue Oct 18, 2005 10:21 AM
Re: The fastest way to filter DBF
Posted: Sun Aug 06, 2006 03: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 Collapse
 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
Badara Thiam
http://www.icim.fr

Continue the discussion

Static archive · New replies & topics via GitHub Discussions