FiveTech Support Forums

FiveWin / Harbour / xBase community
Board indexAll discussionsLogin (GitHub)
Board index FiveWin for Harbour/xHarbour Advice needed for reccount() in scopped and filtered dbfcdx.
lucasdebeltran
Posts: 1303
Joined: Tue Jul 21, 2009 08:12 AM
Re: Advice needed for reccount() in scopped and filtered dbfcdx.
Posted: Wed Feb 04, 2015 08:16 AM

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.

Muchas gracias. Many thanks.



Un saludo, Best regards,



Harbour 3.2.0dev, Borland C++ 5.82 y FWH 13.06 [producción]



Implementando MSVC 2010, FWH64 y ADO.



Abandonando uso xHarbour y SQLRDD.
Enrico Maria Giordano
Posts: 9020
Joined: Thu Oct 06, 2005 08:17 PM
Re: Advice needed for reccount() in scopped and filtered dbfcdx.
Posted: Wed Feb 04, 2015 09:30 AM
This sample:

Code (fw): Select all Collapse
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 (fw): Select all Collapse
         0.00     910404


and this with filter:

Code (fw): Select all Collapse
         2.20      58776


So I don't see any optimization... :-)

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
James Bott
Posts: 4840
Joined: Fri Nov 18, 2005 04:52 PM
Re: Advice needed for reccount() in scopped and filtered dbfcdx.
Posted: Wed Feb 04, 2015 03:04 PM

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

FWH 18.05/xHarbour 1.2.3/BCC7/Windows 10
Enrico Maria Giordano
Posts: 9020
Joined: Thu Oct 06, 2005 08:17 PM
Re: Advice needed for reccount() in scopped and filtered dbfcdx.
Posted: Wed Feb 04, 2015 03:10 PM

James,

understood. Can't test with Clipper anymore, sorry. :-(

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
nageswaragunupudi
Posts: 10733
Joined: Sun Nov 19, 2006 05:22 AM
Re: Advice needed for reccount() in scopped and filtered dbfcdx.
Posted: Wed Feb 04, 2015 04:33 PM
Because this filter
Code (fw): Select all Collapse
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.
Regards



G. N. Rao.

Hyderabad, India
Enrico Maria Giordano
Posts: 9020
Joined: Thu Oct 06, 2005 08:17 PM
Re: Advice needed for reccount() in scopped and filtered dbfcdx.
Posted: Wed Feb 04, 2015 04:44 PM
Rao,

nageswaragunupudi wrote: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
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
James Bott
Posts: 4840
Joined: Fri Nov 18, 2005 04:52 PM
Re: Advice needed for reccount() in scopped and filtered dbfcdx.
Posted: Wed Feb 04, 2015 04:50 PM

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

FWH 18.05/xHarbour 1.2.3/BCC7/Windows 10
Enrico Maria Giordano
Posts: 9020
Joined: Thu Oct 06, 2005 08:17 PM
Re: Advice needed for reccount() in scopped and filtered dbfcdx.
Posted: Wed Feb 04, 2015 05:00 PM
Rao,

nageswaragunupudi wrote: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 (fw): Select all Collapse
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

e.m.giordano@emagsoftware.it
http://www.emagsoftware.it
http://www.emagsoftware.it/emgmusic
http://www.emagsoftware.it/spectrum
http://www.emagsoftware.it/tbosg
Enrico Maria Giordano
Posts: 9020
Joined: Thu Oct 06, 2005 08:17 PM
Re: Advice needed for reccount() in scopped and filtered dbfcdx.
Posted: Wed Feb 04, 2015 05:05 PM
James,

James Bott wrote: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
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
James Bott
Posts: 4840
Joined: Fri Nov 18, 2005 04:52 PM
Re: Advice needed for reccount() in scopped and filtered dbfcdx.
Posted: Wed Feb 04, 2015 05:42 PM
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.
FWH 18.05/xHarbour 1.2.3/BCC7/Windows 10
nageswaragunupudi
Posts: 10733
Joined: Sun Nov 19, 2006 05:22 AM
Re: Advice needed for reccount() in scopped and filtered dbfcdx.
Posted: Wed Feb 04, 2015 05:52 PM
Will you kindly try this sample as it is without changes?
Code (fw): Select all Collapse
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
Regards



G. N. Rao.

Hyderabad, India
Enrico Maria Giordano
Posts: 9020
Joined: Thu Oct 06, 2005 08:17 PM
Re: Advice needed for reccount() in scopped and filtered dbfcdx.
Posted: Wed Feb 04, 2015 06:19 PM
James,

James Bott wrote: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

e.m.giordano@emagsoftware.it
http://www.emagsoftware.it
http://www.emagsoftware.it/emgmusic
http://www.emagsoftware.it/spectrum
http://www.emagsoftware.it/tbosg
Enrico Maria Giordano
Posts: 9020
Joined: Thu Oct 06, 2005 08:17 PM
Re: Advice needed for reccount() in scopped and filtered dbfcdx.
Posted: Wed Feb 04, 2015 06:25 PM
Rao,

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


Yes, there is a speed improvement:

OLD

Code (fw): Select all Collapse
        2.20      58776


NEW

Code (fw): Select all Collapse
         1.84      58776


This is my modified sample:

Code (fw): Select all Collapse
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

e.m.giordano@emagsoftware.it
http://www.emagsoftware.it
http://www.emagsoftware.it/emgmusic
http://www.emagsoftware.it/spectrum
http://www.emagsoftware.it/tbosg
Enrico Maria Giordano
Posts: 9020
Joined: Thu Oct 06, 2005 08:17 PM
Re: Advice needed for reccount() in scopped and filtered dbfcdx.
Posted: Wed Feb 04, 2015 06:28 PM

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

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
nageswaragunupudi
Posts: 10733
Joined: Sun Nov 19, 2006 05:22 AM
Re: Advice needed for reccount() in scopped and filtered dbfcdx.
Posted: Wed Feb 04, 2015 06:38 PM

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.

Regards



G. N. Rao.

Hyderabad, India
Static archive · New replies & topics via GitHub Discussions