Walter,

>It was a myth because in 99% of all cases it did nothing to enhance performance or was slowing queries down. I've identified three cases in where it might be beneficial which are on itself exceptions. The only one that does make sense is the COUNT statement.

You make this statement based upon the way that your app works. Your app is not everyone's app and therefore each developer needs to evaluate the cost/benefit on their own. Not every query need be a NOFILTER result set. So I'd say your 99% estimate is just a tad high. *s*

>A developer should understand when to use indexes and how they are used. This is the main problem here. I wrote the messages back then because I knew how the indexes were used and that the general accepted knowledge was humbug because the logic was false.

Sorry, I don't think that was the case at all. A lot of people understood how the rushmore bitmap works. The deciding performance factor becomes when does the bitwise selectivity do better than the cost to suck the index across the wire vs the time it takes to bring the data row across the wire vs the size of the wire vs the size of the data vs the network loads vs contention for the data. There are just a whole lot of variables involved here Walter.

>Many people tested on their local machine in stead of a network where the network is the bottleneck, not giving reliable indications of its performance in production time. Also many people are not aware of network optimizations like exclusive oplocking so that even when testing over a network they get false measures.

There are cases, and a lot more of them in the past, where the data did actually live on the user machine not a file server. So another big factor is where the data resides.

>I was stunned back in 1999 that so little people (in fact I encountered none at the time), who really understood how rushmore optimized queries. Even from the side of MS there is no usable explanation telling you this.

You expect Microsoft to release great detail on one of the things that has always made FoxPro faster than the competition? Don't ever expect any company to release such intellectual property.

>If people knew a littlebit more about databases and indexes in general it would not be such a great problem.

Basically one should always learn and test for themselves to see how any given factor affects their performance. They should not accept anyone's statement on the subject as an undisbuteable truth.

>Unfortunately, the binary index is a miss. Though much smaller it really does not adress the problem: Low selectivity and therefore it does not change much to the story. If the deleted() tag is not going to weed out (many) deleted() records it use is useless.

Is this based on actual tests or just a supposition?

>People are given a false indication that full optimization is better than partial optimization.

Again developers need to see which is actually faster in their environment.