Thomas,

Mike is right.

I was the first here on the forum in the 90-ties touching this subject. The thread can still be found.

If there are no or very few deleted record an index tag on deleted() is only going to bite you, in multi user, LAN network setting with large tables.

Since VFP has no cost-base query optimizer (huge difference with SQL server), but rather will force every suitable index to be used in query, it will drag a lot more information across the network than neccesary to process the query than neccesary. This specifically applies to the index in deleted() of there are no or very few deleted records, the index only costs network bandwidth, memory and CPU resources.

You won't see the problem (as much) with small tables, locally or single user networked application because extra resources needed are hardly making any impact.

The only command I know hugely benfitting from 'full optimization' with an index on deleted() is the COUNT command and COUNT() aggregations in SQL select. That is because then only the index (and thus not the table) is used to count the number of records.

But if you're facing a performance problem on large tables over a LAN (or worse) network, you might make a significant improvement to get rid of the index on deleted().

As for the normal index vs binary index: Sure it reduces the network I/O significantly, but the problem is still there: The query requests and uses more resources than neccesary to perform its task.
I've never bothered to index on deleted records as normally my tables contain no or very few deleted record. In that case there is theoretically simply no case to be made for it (outside the COUNT() statement).


Walter,




>>>If you need an index on a logical field or on deleted(), make sure to add the Binary keyword when you create it. That will make the index much smaller, meaning much faster.
>>
>>Not faster than not having the index at all, especially when other filters have already identified the records.
>
>When binary came out, we measured perf with traditional, binary and no index on deleted() on sizable data sets of a real application, as I already had a test harness based on QueryPerf in place. We weighed the results of the different data sets to the largest, as runtime duration was rising more than linear.
>
>Binary was the best compromize, leading to smallest total time and no really bad performance in single measurements -
>was never the worst / an outlier. IIRC we added a more than a dozen binary indices to tables where we had previously deleted a traditional index on deleted() - after running perf tests, total table count > 300.
>
>YMMV
>>
>>>
>>>>The more it makes sense for me to get rid of it. Thank you.
>>>>
>>>>>Any index on a binary value will generally not be very effective. I've heard some people say that an index on DELETED() is effective if you have a high number of deleted records, but never tested this myself.
>>>>>
>>>>>