Filtering data in a DBF-file

[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.

[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

[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

[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

[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.

[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!!

[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.

[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

[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.

[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.

[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.

[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

[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.

[PeterHarmes]

Have you ever tried it in a Citrix environment?

[Gale FORd]

Yes, I use it all the time. Each user has its own profile location.