Page 1 of 1
The fastest way to filter DBF
Posted: Tue Aug 01, 2006 9:30 pm
by Ehab Samir Aziz
I am extracting certain records from huge DBF (8000 Record) and it is so slow .
Code: Select all
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
Posted: Tue Aug 01, 2006 9:49 pm
by 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())
Posted: Wed Aug 02, 2006 1:54 am
by 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.
Posted: Wed Aug 02, 2006 4:34 pm
by 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
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)
Posted: Wed Aug 02, 2006 4:43 pm
by Enrico Maria Giordano
Try using a conditional index:
INDEX ON yourkey TO temp FOR &(cFilter)
EMG
Posted: Wed Aug 02, 2006 4:57 pm
by Ehab Samir Aziz
My key is varied depend on the conditions of the filter.
Posted: Wed Aug 02, 2006 5:16 pm
by 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
Posted: Fri Aug 04, 2006 12:35 pm
by Ehab Samir Aziz
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.
Posted: Fri Aug 04, 2006 12:46 pm
by Enrico Maria Giordano
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
Posted: Fri Aug 04, 2006 1:02 pm
by 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
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())
Posted: Fri Aug 04, 2006 5:07 pm
by Ehab Samir Aziz
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())
Posted: Fri Aug 04, 2006 7:03 pm
by Enrico Maria Giordano
Try removing this code. It is redundant:
Code: Select all
bFilter = "{||("+cFilter+")}"
cFilter = "("+cFilter+")"
DBSETFILTER(&bFilter,&cFilter)
mete->(dbgotop())
EMG
Posted: Sat Aug 05, 2006 11:35 am
by 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.
Posted: Sat Aug 05, 2006 12:02 pm
by Enrico Maria Giordano
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
Re: The fastest way to filter DBF
Posted: Sun Aug 06, 2006 3:21 pm
by Badara Thiam
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