Hi Elmer
Using an index with a filter especially one with a NOT is not Rushmore optimizable. However, having an INDEX ON
FOR DELETED() TAG ELMER would be useful in this case. To determine the number of deleted records, just
LPARAMETERS tcAlias
LOCAL lcAlias, lcDeleted, lcOrder
lcAlias = SELECT()
lcDeleted = SET("DELETED")
SET DELETED OFF
SELECT (tcAlias)
lcOrder = ORDER()
SET ORDER TO TAG ELMER
COUNT TO lnCount
?reccount()-m.lnCount
SET ORDER TO &lcOrder.
SELECT &lcSelect.
SET DELETED &lcDeleted.I think it fair to assume there will be fewer deleted records.
My p3 650 laptop takes .002 seconds to do the count on a table with 1 million records and every thousandth record is deleted.
>If it is actually a "VIEW", and set deleted is on, you can use reccount(), if you are talking about an open table with a filter, then your function should be sufficient. I guess you could optimize the count with an index on !deleted() and use count for !deleted() but depending on how often you use this function, it may not be worth the additional overhead and may cause a performance hit in other places.
>
>
>>When SET DELETED is "ON", RECCOUNT() by design still returns the total number of records in the table. I have had for a long time a dire need for second parameter on RECCOUNT() to return the number of records not including those that are deleted. I created a function called RECCOUNTD, and it works great 99% of the time. When tables exceed several hundred thousand records lags of 5-10 seconds or more can be seen the first time the function is called on the table. Here is my code:
>>
>>FUNCTION ReccountD
>>PARAMETERS Reccount_WhichDB
>>
>>** REMEMBER THAT THIS FUNCTION DOES NOT CLEAR FILTERS!!!!!!!
>>
>>** STORE ENVIRONMENT
>>Reccount_OldSel = SELECT()
>>
>>IF NOT EMPTY(Reccount_WhichDB)
>> SELECT (Reccount_WhichDB)
>>ENDIF
>>Reccount_OldRecNo = RECNO()
>>
>>IF Reccount_OldRecNo > 0
>> COUNT TO Reccount_Return
>>
>> ** RESET ENVIRONMENT
>> =GoRec(Reccount_OldRecNo)
>> SELECT (Reccount_OldSel)
>>
>> RETURN Reccount_Return
>>ELSE
>> ** RESET ENVIRONMENT
>> SELECT (Reccount_OldSel)
>> RETURN 0
>>ENDIF
>>
>>
>>** END CODE
>>
>>Originally, I used SELECT COUNT(*) code, but it was ultimately slower for some reason and this code also accounted for filters (something that I desired).
>>
>>Does anyone have any optimization ideas and/or is there any chance that MS can whip up a "COUNT()" function that tells how many records are in the current view if you were essentially to browse the table.
>>
>>Thanks,
>>
>>JohnO
