Page 1 of 2
Filtering data in a DBF-file
Posted:
Fri Mar 04, 2011 10:12 amby driessen
ello,
I'm looking for an alternative for "SET FILTER TO".
I have some very huge DBF-files and using "SET FILTER TO" is soooooo slow.
You may think : why don't you use an index in the CDX-file ? I can't use an index as such because there are so many possibilities that that data need to be filtered. It is just impossible to build a tag in the CDX-file for any possibility.
Anyone any idea ?
Thanks.
Re: Filtering data in a DBF-file
Posted:
Fri Mar 04, 2011 10:41 amby MarcoBoschi
Please,
write some examples of SET FILTER TO you have to do
Other questions:
- from one table?
- from several related tables ?
- in Lan?
- directly in a local volume?
bye
Re: Filtering data in a DBF-file
Posted:
Fri Mar 04, 2011 10:53 amby PeterHarmes
I create a scope that will filter most of the data (for example, if it was a transaction file, scope on start/end date) then create an index using the USECURRENT parameter.
Dont know if you can use something like that?
Best regards,
Pete
Re: Filtering data in a DBF-file
Posted:
Fri Mar 04, 2011 11:14 amby kokookao2007
hi:
What is your RDD ?
What did you do after Filter ? only browse ? Need edit ?
1)RDD "DBFCDX: CAN FILTER MORE THAN 2 TRY TO
SET SCOPE TO aaa
SET FILTER TO &cCond
2)RDD "ADS": I don't Know how to filter more than one table.
SET SCOPE TO aaa
SET FILTER TO &cCond ==>need tansfer to AdsSetAof( <xpr> )
My customer has a dbf file with 5 index files,4 relation file more 2GB , still work .
Woule you give your sample?
--
Best Regards
--------------------------------------------------------------
kokoo
Re: Filtering data in a DBF-file
Posted:
Fri Mar 04, 2011 12:05 pmby driessen
Hello guys,
Thanks a lot for your answer.
Here is my example :
- Code: Select all Expand view
cFilt := "(AG->AGDATUM = US->UPRDATV" + IF(US->UVERTODO,")"," .OR. (AG->AGDATUM < US->UPRDATT .AND. !AG->AGAFGESL .AND. AG->AGAGRAP))")
IF UPPER(ALLTRIM(UsTab[LEN(UsTab)])) <> "ALLE GEBRUIKERS"
cFilt += " .AND. ("
FOR i=1 TO LEN(UsTab)
cFilt += ("VAL(AG->AGUSER) = " + ALLTRIM(RIGHT(UsTab[i],7)))
IF i <> LEN(UsTab) ; cFilt += " .OR. " ; ENDIF
NEXT
cFilt += ")"
ENDIF
//Order key = "AGUSERNS+AGUSER+LEFT(AGBTIJD,2)+RIGHT(AGBTIJD,2)+LEFT(AGETIJD,2)+RIGHT(AGETIJD,2)+DTOS(AGDATUM)"
SELECT AG
SET ORDER TO 21
SET FILTER TO &cFilt
It works very well, but soooo slowly.
The order key :
AGUSERNS = name of person
AGUSER = the person's
internal code (C 7)
The order has to be that way because I need to make calculations per person and per day and the result has to be shown alphabetically.
Thanks a lot in advance for your help.
Re: Filtering data in a DBF-file
Posted:
Fri Mar 04, 2011 12:15 pmby PeterHarmes
I would create a scope on AGDATUM - then index on AGUSERNS + AGUSER USECURRENT FOR UserFunc()
UserFunc would be your filter condition returning .T./.F.
If there is still a long delay, you could display a progress bar using EVAL ... so that your users can see that something is happening - although i have found that indexing is quicker without the EVAL
Hope that helps!!
Re: Filtering data in a DBF-file
Posted:
Fri Mar 04, 2011 1:15 pmby driessen
Peter,
Thanks a lot for your answer.
But how can I use another index when I have created a scope on AGDATUM ?
How does USERCURRENT FOR UserFunc() work ?
Can you give me an example of displaying a progress bar by using EVAL ?
Thanks a lot.
Re: Filtering data in a DBF-file
Posted:
Fri Mar 04, 2011 2:04 pmby MarcoBoschi
Driessen,
I've made this little test:
ANNOUNCE RDDSYS
FUNCTION MAIN()
SET DELETED ON
SET EXCLUSIVE OFF
SELECT 0
USE clihs
SET INDEX TO clihs
? SECONDS()
INDEX ON clifor TAG CLIFOR TO filtrato FOR prov = "PD" .AND. SUBSTR( clifor , 1 , 2 ) = "AB"
? SECONDS()
RETURN NIL
INIT PROCEDURE RddInit
REQUEST DBFFPT
REQUEST DBFCDX
rddSetDefault( "DBFCDX" )
RETURN
In my client it takes 6" to create index
seconds() 54020.32
seconds() 54026.31
In my server it takes exactly 0,13 seconds !!!! Just a blink!
seconds() 53999.42
seconds() 53999.55
Continue to use the SET FILTER but the server is a good choice
Bye
Re: Filtering data in a DBF-file
Posted:
Fri Mar 04, 2011 2:09 pmby PeterHarmes
Driessen,
Here is an example of creating an index using USECURRENT and progress bar:
Note - CmxKeyCount() is dbOrderInfo(DBOI_KEYCOUNTRAW, cBag, xOrder) - I used to use comix!
- Code: Select all Expand view
//Create Scope
MsgMeter( { | oMeter, oText, oDlg, lEnd | BSeekIndex( oMeter, oText, oDlg, @lEnd ) } )
STATIC FUNCTION BSeekIndex( oMeter, oText, oDlg, lEnd )
Local TmpIndx := ""
Private IndxCount := 0
oMeter:nTotal := BROKERED->(CmxKeyCount())
CursorWait()
TmpIndx := TempPath()+"BRKSEEK.CDX"
IF FILE(TmpIndx)
FERASE(TmpIndx)
ENDIF
TmpIndx := TempPath()+"BRKSEEK"
SELECT BROKERED
INDEX ON STR(SITE_ID,2,0)+ORDER_NO+STR(LINE_NO,4) TO &TmpIndx. FOR !DELETED() .AND. BrokSeekVal() USECURRENT EVAL ( IndxCount++, oMeter:Set( IndxCount ), SysRefresh(), ! lEnd )
SET INDEX TO &TmpIndx.
CursorArrow()
RETURN NIL
My function BrokSeekVal then filters the data:
- Code: Select all Expand view
FUNCTION BrokSeekVal
LOCAL RetVal := .T.
IF !Empty(mCustFrom) .OR. !Empty(mCustTo)
RetVal := LEFT(BROKERED->ACNT_NBR,LEN(ALLTRIM(mCustFrom))) >= Alltrim(mCustFrom) .AND. ;
LEFT(BROKERED->ACNT_NBR,LEN(ALLTRIM(mCustTo))) <= Alltrim(mCustTo)
ENDIF
IF RetVal .AND. ;
(!Empty(mGradeFrom) .OR. !Empty(mGradeTo))
RetVal := LEFT(BROKERED->GRADE,LEN(ALLTRIM(mGradeFrom))) >= Alltrim(mGradeFrom) .AND. ;
LEFT(BROKERED->GRADE,LEN(ALLTRIM(mGradeTo))) <= Alltrim(mGradeTo)
ENDIF
IF RetVal .AND. ;
(!Empty(mOrdFrom) .OR. !Empty(mOrdTo))
RetVal := LOWER(LEFT(BROKERED->CUST_ORDER,LEN(ALLTRIM(mOrdFrom)))) >= Lower(Alltrim(mOrdFrom)) .AND. ;
LOWER(LEFT(BROKERED->CUST_ORDER,LEN(ALLTRIM(mOrdTo)))) <= Lower(Alltrim(mOrdTo))
ENDIF
IF RetVal .AND. ;
(!Empty(mPurchFrom) .OR. !Empty(mPurchTo))
RetVal := LEFT(BROKERED->PURCH_CUST,LEN(ALLTRIM(mPurchFrom))) >= Alltrim(mPurchFrom) .AND. ;
LEFT(BROKERED->PURCH_CUST,LEN(ALLTRIM(mPurchTo))) <= Alltrim(mPurchTo)
ENDIF
IF RetVal .AND. ;
(!Empty(mTranspFrom) .OR. !Empty(mTranspTo))
RetVal := LEFT(BROKERED->HAULIER,LEN(ALLTRIM(mTranspFrom))) >= Alltrim(mTranspFrom) .AND. ;
LEFT(BROKERED->HAULIER,LEN(ALLTRIM(mTranspTo))) <= Alltrim(mTranspTo)
ENDIF
RETURN RetVal
If you needed to use another index, you will have to swap between indexes using dbsetorder - but obvioulsy the "filter" will only apply to the newly created index.
Re: Filtering data in a DBF-file - SOLVED
Posted:
Fri Mar 04, 2011 8:47 pmby driessen
Peter,
Thank you very, very much.
Your suggestion by building a second CDX-file (which can be build locally) is working fantastically.
Very fast.
I never have used SET INDEX TO. Much better than filtering.
I'm very greatful to you.
Re: Filtering data in a DBF-file
Posted:
Fri Mar 04, 2011 11:43 pmby Gale FORd
Don't forget you can add the "Temporary" argument to the index on command. It will automatically create a temporary index and erase it when you are done.
Re: Filtering data in a DBF-file
Posted:
Tue Mar 08, 2011 9:20 amby PeterHarmes
No probem, glad I could help.
Gale, I never knew about the TEMPORARY argument, I can imagine that if it creates it in memory the index creation would be faster than writing to disk, but I assume this should only be done on small databases or small selection criteria
Best regards,
Pete
Re: Filtering data in a DBF-file
Posted:
Tue Mar 08, 2011 2:33 pmby Gale FORd
It does not automatically create it in memory but I think it depends on the RDD. Here is a brief description of that option.
TEMPORARY
If this option is specified, a temporary index is created which is automatically destroyed when the index is closed. The temporary index may be created in memory only or in a temporary file. This lies in the responsibility of the RDD used for index creation.
For me it creates the temp index in the user folder in c:\documents and settings.
Re: Filtering data in a DBF-file
Posted:
Tue Mar 08, 2011 2:41 pmby PeterHarmes
Have you ever tried it in a Citrix environment?
Re: Filtering data in a DBF-file
Posted:
Tue Mar 08, 2011 6:00 pmby Gale FORd
Yes, I use it all the time. Each user has its own profile location.