Dual index for setscope.()

[TimStone]

I have a report that is taking a very long time to create. The reason is that it uses a filter on one field, and an index on another.

The first field is by NAME, and we want to do the report grouping all records by name, and breaking when the name changes.
The second field for selection is by date, and I use a filter to look only at records within a range of dates.

There might be 500,000 records in the .dbf. If I use a filter, it must go through each record to see if it has a date in the range specified. This is painfully slow

It would be great to use seascape...( ) to limit the records to just those in the date range, but then the break is with each date, not with each name. I tried the index as DTOS( itemdate ) plus item name

Using setscopetop( beginningdate ) and setscopebottom( endingdate ) selects the correct records, but breaks with every day change plus every name change.

Any ideas on how to accomplish this ?

[Jack]

Hi,
In this situation , i make a temorary index with the for clause :
index on NAME to TEST FOR DATE > ... .and. DATE < ....

Good luck
Philippe

[nageswaragunupudi]

DBFCDX optimizes filters if we have indexes on the fields used in the filter expression. In the above case, we need two index tags

Code: Select all  Expand view  RUN

field NAME,ITEMDATE

INDEX ON UPPER(NAME) TAG NAME   // do not use FIELD-> or ANY ALIAS->
INDEX ON ITEMDATE TAG ITEMDATE  // do not use DTOS()
INDEX ON DELETED() TAG DELETED
 

On a 500,000 record DBF creation of these 3 index tags may take betwen 2 to 3 seconds. In any case this is one time job and these 3 tags would be included in the compound index.
Note: Do not use expressions like DTOS(), FIELD->, etc.

Now, set up the DBF like this:

Code: Select all  Expand view  RUN

field ITEMDATE
local ...


USE YOURDBF NEW SHARED VIA "DBFCDX"
SET ORDER TO TAG NAME
SET FILTER TO ITEMDATE >= {^ 2018/01/01} .AND. ITEMDATE < {^ 2018/03/01} .AND. !DELETED()
GO TOP // this may take between 2 and 3 seconds

// do not forget this next line
TPreview():lListViewHide := .t.  // List view takes hell of a time on large previews

// now create the report

 

You use the dates you want.

RDD sees that the field name used in the filter expression is ITEMDATE. It also checks to see if there is an index on the same name, i.e., ITEMDAE and uses the index to construct a bitmap record list for the optimized filter.

Now the report will run faster

[TimStone]

Thank you for the input. I will work with those today,.