Advice needed for reccount() in scopped and filtered dbfcdx.

[lucasdebeltran]

Dear Mr. Nages,

From previous posts from you I understood that SET DELETED ON in a network scenareo and xBrowse causes some delay, so that´s the reason to include the For !Deleted() clause on the index command.

Is it correct?.

Thank you.

[Enrico Maria Giordano]

This sample:

Code: Select all  Expand view  RUN

REQUEST DBFCDX


FUNCTION MAIN()

    LOCAL nSec, nKey

    RDDSETDEFAULT( "DBFCDX" )

    IF !FILE( "CORSE.CDX" )
        USE CORSE
        INDEX ON DTOS( FIELD -> data ) + UPPER( FIELD -> cliente ) TO CORSE
        CLOSE
    ENDIF

    USE CORSE INDEX CORSE

    SET FILTER TO FIELD -> cliente = "A"

    nSec = SECONDS()

    nKey = ORDKEYCOUNT()

    ? SECONDS() - nSec, nKey

    CLOSE

    RETURN NIL

displays this without filter

Code: Select all  Expand view  RUN

        0.00     910404

and this with filter:

Code: Select all  Expand view  RUN

        2.20      58776

So I don't see any optimization...

EMG

[James Bott]

Enrico,

Hmm, you can't compare with and without a filter to see optimization. It seems you would have to compare the filter compiled with xHarbour (with optimization) v.s. Clipper (without optimization).

James

[Enrico Maria Giordano]

James,

understood. Can't test with Clipper anymore, sorry.

EMG

[nageswaragunupudi]

Because this filter

Code: Select all  Expand view  RUN

SET FILTER TO FIELD -> cliente = "A"

can not be optimized at all.

As I said earlier, DBFCDX provides capabilities of optimization, provided the programmer wants to avail the benefits of these capabilities. In this case we can not say DBFCDX is slow.

If there is an index on CLIENTE then SET FILTER TO CLIENTE = "A" can be fully optimized.
If there is an index on UPPER(CLIENTE) then SET FILTER TO UPPER(CLIENTE) = 'A' can be fully optimized.

The guidelines I indicated above to set optimized filters/where clauses are universal and are applicable to all RDBMSs as well.

[Enrico Maria Giordano]

Rao,

If there is an index on CLIENTE then SET FILTER TO CLIENTE = "A" can be fully optimized.
If there is an index on UPPER(CLIENTE) then SET FILTER TO UPPER(CLIENTE) = 'A' can be fully optimized.

If I had an index on UPPER(CLIENTE) I wouldn't need of filters at all. I would just use scopes. We can't have an index for any possible filter.

EMG

[James Bott]

Enrico,

Consider:

Assuming there is an index on client, then this should be optimized:

set filter to client ="A" .or. client="M"

And I don't think you can do this with scopes.

James

[Enrico Maria Giordano]

Rao,

If there is an index on CLIENTE then SET FILTER TO CLIENTE = "A" can be fully optimized.
If there is an index on UPPER(CLIENTE) then SET FILTER TO UPPER(CLIENTE) = 'A' can be fully optimized.

I don't see any speed improvement in the modified sample below.

Code: Select all  Expand view  RUN

REQUEST DBFCDX


FUNCTION MAIN()

    LOCAL nSec, nKey

    RDDSETDEFAULT( "DBFCDX" )

    IF !FILE( "CORSE.CDX" )
        USE CORSE
        INDEX ON UPPER( FIELD -> cliente ) TO CORSE
        CLOSE
    ENDIF

    USE CORSE INDEX CORSE

    nSec = SECONDS()

    SET FILTER TO UPPER( FIELD -> cliente ) = "A"

    nKey = ORDKEYCOUNT()

    ? SECONDS() - nSec, nKey

    CLOSE

    RETURN NIL

EMG

[Enrico Maria Giordano]

James,

Consider:

Assuming there is an index on client, then this should be optimized:

set filter to client ="A" .or. client="M"

And I don't think you can do this with scopes.

Assuming that the filter above could be optimized, it would only be a specific case. Imagine a dialog full of GETs. You want your user to search on any fields and combinations. You just can't create such a number of indexes, can you?

EMG

[James Bott]

Enrico,

You want your user to search on any fields and combinations.

Agreed, but I don't see any logical way you can search for any combination of fields in a filter without reading all the records for at least some of the combinations.

I think the point is, that some filters will be optimized if there is a useful index. And this is better than no optimization ever--which was the old way.

You also have to consider probability. There are a few combinations of fields that users will search often, and some that they will never search. Think of the 80/20 rule-- 20% of the combinations will be searched 80% of the time. You could log the searches for awhile, then make sure you have indexes that will help in those 20% of the cases. Hmm, now if you wanted to get really sophisticated, you could build a routine that automatically tracks the searches and then designs indexes to speed them up. I like it.

[nageswaragunupudi]

Will you kindly try this sample as it is without changes?

Code: Select all  Expand view  RUN

REQUEST DBFCDX


FUNCTION MAIN()

    FIELD CLIENTE
    LOCAL nSec, nKey

    RDDSETDEFAULT( "DBFCDX" )


    FERASE( "CORSE.CDX" )
   
    USE CORSE EXCLUSIVE
    INDEX ON UPPER(CLIENTE) TAG CLIENTE
    INDEX ON DELETED() TAG DELETED
    CLOSE

    USE CORSE SHARED

    nSec = SECONDS()

    SET FILTER TO UPPER(CLIENTE) = "A" .AND. !DELETED()

    nKey = ORDKEYCOUNT()

    ? SECONDS() - nSec, nKey

    CLOSE

    RETURN NIL
 

[Enrico Maria Giordano]

James,

Agreed, but I don't see any logical way you can search for any combination of fields in a filter without reading all the records for at least some of the combinations.

I've always used conditional indexes created "on the fly" with very good results. The searches demanded by my clients are normally very complex, requiring functions calls inside the key and the FOR conditions.

EMG

[Enrico Maria Giordano]

Rao,

Will you kindly try this sample as it is without changes?

Yes, there is a speed improvement:

OLD

Code: Select all  Expand view  RUN

       2.20      58776

NEW

Code: Select all  Expand view  RUN

        1.84      58776

This is my modified sample:

Code: Select all  Expand view  RUN

REQUEST DBFCDX


FUNCTION MAIN()

    LOCAL nSec, nKey

    RDDSETDEFAULT( "DBFCDX" )

    IF !FILE( "CORSE.CDX" )
        USE CORSE
        INDEX ON UPPER( FIELD -> cliente ) TO CORSE
        CLOSE
    ENDIF

    USE CORSE INDEX CORSE

    SET ORDER TO 0

    nSec = SECONDS()

    SET FILTER TO UPPER( FIELD -> cliente ) = "A"

    nKey = ORDKEYCOUNT()

    ? SECONDS() - nSec, nKey

    CLOSE

    RETURN NIL

As you can see, the only required change is SET ORDER TO 0.

EMG

[Enrico Maria Giordano]

I would explain that my new sample is as fast as your. So nothing related to optimizations.

EMG

[nageswaragunupudi]

Mr EMG

You modified my code.
For once please compile my code exactly as it is without changing even a single alphabet and then see the difference. I guarantee a difference in speed.
BTW may I know the total number of records?
Or please send me your dbf ziipped.