>>>>>>>>>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.
>>>>>
>>>>>Are in all these cases, were normal index tags used, or binary index tags? If binary index tags were not used, you really compare apples to oranges. The difference is huge, to say the least. Others have performed similar tests WITH binary index tags on Deleted(), and their conclusion was that binary index tags in most cases resulted in huge speed gains.
>>>>
>>>>Compared to non-binary indexes, sure. Compared to not having the indexes at all? No. I am one of the people who made a lot of study of indexes.
>>>
>>>Why don't you simply answer my question? As I understand it, you keep on comparing apples to oranges.
>>>
>>>Having a "normal" index on deleted() is normally a waste, agreed. Having a binary index on deleted() is a completely different discussion, and should be treated accordingly. When the binary index option was added, it was done for a reason. If you don't want to take advantage if it, fine, But please don't behave like this feature doesn't exist by totally ignoring it.
>>
>>No need to be rude. If you MUST have a useless index, of course, make sure it's as small as possible. That's your argument.
>>
>>I know the precisely why the feature exists. It was built to improve a situation that many people believe, which is mostly a myth. If you have one deleted record in 5 million records and your queries are regularly producing small sets of records, only 1 in 5 million times and possibly never, will that deleted index help at all.
>
>Please reread my replies. If you find any of them rude, I promise that it wasn't intentional. My ONLY point is that you must compare with identical conditions. If you compare a steam engine with a turbo charged engine running on rocket fuel, of course you will get different results. A binary index is only one eighth in size compared to a normal index, which you don't take into consideration. That's why you compare apples to oranges.
>
>Also know that I never delete records, so I never use index tags on deleted(). However, that's not relevant in this argument. 'nuff said.

I disagree. The comparison must be whether you use an index on deleted or not - not whether it is binary or not. If the index is used on a local machine versus over a LAN there is a performance difference again. If you construct your system properly, it is not needed.


>
>>>
>>>>>
>>>>>
>>>>>>>
>>>>>>>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.
>>>>>>>>>>>
>>>>>>>>>>>