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

[Horizon]

I use this syntax to find out reccount for scoped and filtered dbfcdx file.

Code: Select all  Expand view

   
    USE MYFILE INDEX "SSTAR"    
    SET SCOPE TO DTOS(xTARIH), DTOS(xTARIH)

    IF !Empty(SSART)
           SET FILTER TO &SSART
    ENDIF
    GO TOP
    nCount := 0
    DO WHILE !EOF()
        nCount++
        SKIP
        SysRefresh()
    ENDDO
 

Is there any other method to find out reccount?

Thanks.

[Enrico Maria Giordano]

Hakan,

you can try OrdKeyCount() but keep in mind that it doesn't respect filters, only scopes and index FOR clause conditions.

EMG

[nageswaragunupudi]

OrdKeyCount() respects not only scopes and (for cond indexes) but also filters.
But it does not exclude deleted() records when set deleted on ( this is how we use normally ).
So if we set filter to "SET FILTER TO !DELETED()" then the OrdKeyCount() excludes deleted records also.

[Enrico Maria Giordano]

Rao,

OrdKeyCount() respects not only scopes and (for cond indexes) but also filters.

You're right!

EMG

[James Bott]

Since the filter info is not contained in the index, then ordkeycount() would have to read all the records in the scope in order to determine those in the filter. This must be rather slow since it is all disk access.

James

[Enrico Maria Giordano]

James,

Since the filter info is not contained in the index, then ordkeycount() would have to read all the records in the scope in order to determine those in the filter. This must be rather slow since it is all disk access.

This is true...

EMG

[nageswaragunupudi]

would have to read all the records

Not at all so with the present day optimized filters in DBFCDX and other advanced RDDs

Decades back we were working with Clipper with DBFNDX, DBFNTX RDDs. Those days if a filter is set, RDD reads all records and skips filtered and deleted records. Yes, that was deadly slow. Gurus used to say avoid filters like plague.

Then Foxpro came up with Rushmore technology, introducing bitmapped filtering technique, initially optimized with the help of indexes. That changed the entire scenario. Filtering was lightning fast.

Comix and SIX RDDs came into market providing the same technology for Clipper. Now no limits on filtering. Later Clipper bought Comix from Loadstone and incorporated the same in DBFCDX in Clipper 5.3, though Clipper's adoption is less powerful than the original Comix.

Present 32-bit (x)Harbour also provide the same functionality. OrdKeyCount() even with filters is very fast and does not have to read all records in the scope.

[Enrico Maria Giordano]

Rao,

I don't think filters are fast with standard xHarbour's DBFCDX. But I strongly hope to be wrong...

EMG

[James Bott]

Nages,

Thanks for the explanation.

OrdKeyCount() even with filters is very fast and does not have to read all records in the scope.

I would think this would have to be "may not" instead of "does not." If, information in the filter is not contained in any of the indexes, then I don't see how it could avoid reading all the records in the scope.

James

[lucasdebeltran]

Hello,

In a dbf with about 50.000 records, Harbour and RDDCDX filters are slow, and much more in a network scenario.

In network mode, I just do:

Code: Select all  Expand view

  SET EXCLUSIVE OFF
   
   USE (cMyPath+"AUXILIAR")    NEW  ALIAS "AUXILIAR"
   AUXILIAR->(OrdSetFocus("AUXILIAR1"))

Maybe I am missing something.

Thank you.

[nageswaragunupudi]

Instead of 50,000 records let us test on 100.000 records (approx).

Please compile and run this program:
Copy \fwh\samples\customer.dbf to the folder where the exe is located. Or build this program in \fwh\samples folder.

Code: Select all  Expand view

#include "fivewin.ch"

REQUEST DBFCDX

static function TestFilter

   field AGE, STATE, CITY
   local n, nSecs, nCount

   SET EXACT OFF
   SET DELETED ON
   SET EXCLUSIVE OFF

   RDDSETDEFAULT( "DBFCDX" )

//---

   ? "Start Building 100,000 recs dbf"

   USE CUSTOMER EXCLUSIVE
   COPY TO CUST100
   CLOSE DATA
   USE CUST100 EXCLUSIVE

   for n := 1 to 199
      APPEND FROM CUSTOMER
   next
   CLOSE DATA

   ? "Index big dbf"
   USE CUST100 EXCLUSIVE
   INDEX ON AGE TAG AGE
   INDEX ON STATE TAG STATE
   INDEX ON UPPER(CITY) TAG CITY
   INDEX ON DELETED() TAG DELETED
   CLOSE DATA

//-----

   ? "Open BigDbf"
   USE CUST100 NEW SHARED
   ? "Apply filter and get count"
   nSecs    := Seconds()
   SET FILTER TO AGE < 70 .AND. ( STATE = "NY" .OR. STATE = "WA" ) .AND. UPPER(CITY) < "M" .AND. ! DELETED()
   GO TOP
   ? "Filtered in " + Str( Seconds() - nSecs ) + " secs"

   nSecs    := Seconds()
   nCount   := OrdKeyCount()
   nSecs    := Seconds() - nSecs

   FWDBG LastRec(), nCount, nSecs
   SET ORDER TO TAG STATE
   GO TOP
   XBROWSER "CUST100"
   CLOSE DATA

return nil
 

Check the time taken to execute OrdKeyCount() and experience the xbrowse.
I built the program with Harbour. It took 0.40 seconds (less than 1/2 second) to count 1400 filtered records in a total 99,200 records. Do we still say that the RDD "reads all records" for counting ?

[nageswaragunupudi]

If, information in the filter is not contained in any of the indexes, then I don't see how it could avoid reading all the records in the scope.

Filter information is never stored in indexes.

RDD builds a record list (whatever name it is called) of which each bit represents one record. RDD sets on and off the bits evaluating the filter condition based on index expressions and index values. Where the filter condition is not fully optimized, RDD scans the remaining records (maybe's) and eliminates filtered records. This is normally done as and when required. OrdKeyCount() also forces this operation.

Finally counting filtered records is only counting "on" bits of the internal list.

DBFCDX RDD (and some other advanced RDDs) basically has good optimization capabilities. It is for the programmer to make the best use of these capabilities.

1. Do not use aliases in index experssions or filter expressions.
INDEX ON STATE is better than INDEX ON CUST->STATE or INDEX ON FIELD->STATE
2. Do not use memory variables or UDFs in the filter experssions. Instead use literal constants.
3. Left side of a comparison expression should match exactly an index expression.
When there is an index on UPPER(CITY), better we use UPPER(CITY) < "M" than CITY < "M"
4. If the filter expression contains optimzable and not optimizable expressions, let us start with optimizable expressions first. If some expressions result in fewer filtered records then let us have such experssions first. (I am not very sure how our RDD works, but this is by long years of experience in building where/filter clauses on large RDBMSs)
5) If targetting networked environments, keep the size of index files to the minimum. This is very important

[lucasdebeltran]

Dear Mr. Nages,

Thank you very much for your indications.

And, wouldn´t be better for performance to SET DELETED OFF and create indexes like

Code: Select all  Expand view

INDEX ON FIELD->CODIGO TAG EMISOR1  FOR !Deleted()

?.

(or INDEX ON CODIGO TAG EMISOR1 FOR !Deleted())?.

Thank you.

[Horizon]

Thank you

I have solved my problem like that

Code: Select all  Expand view

   USE MYFILE INDEX "SSTAR"    
    SET SCOPE TO DTOS(xTARIH), DTOS(xTARIH)

    IF !Empty(SSART)
           SSART += " .AND. !DELETED()"
           SET FILTER TO &SSART
    ENDIF
    GO TOP
    nCount := OrdKeyCount()

This is more faster than my old code.

[nageswaragunupudi]

Conditional indexes are not used for optimizing filters.
It does not make any difference whether SET DELETED is OFF or ON for indexing.
Create normal non-conditional indexes on the disk.

Code: Select all  Expand view

field CITY  // declaration like local, static, etc

INDEX ON UPPER(CITY) TAG CITY // Please note: I am not using FIELD->CITY

For every large table, create one TAG as a matter of routine:

Code: Select all  Expand view

INDEX ON DELETED() TAG DELETED

When we do not need any filter use
SET FILTER TO !DELETED()

When we need a filter, suffix " .AND. !DELETED()" to the filter condition:
SET FILTER TO <condition> .AND. !DELETED()

We may create conditional indexes on a temporary basis at runtime only to improve navigation speeds for browses.