Mike Yearwood
Toronto, Ontario, Canada
>>>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.
I've done a lot of removal of the deleted index and only saw performance gains - except where someone was doing an unfiltered count with set deleted on, which became - "count for not deleted()". There are always explanations of why the deleted index had a positive effect, but these reasons were not proof.
>
>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.
>>>>>
>>>>>
Previous
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only