Walter,

Let me say what I've been saying all along perhaps a little more plainly.

If the time to retrieve the binary index and apply it to the current Rushmore bitmap is less than the time it takes to retrieve the excess records and evaluate if they should remain in the final result set then there will be a net gain in performance.

>No, I'm not. OTOH you seem to fail to recognize that VFP is doing some stuff that is of no or little use. If no OR ONLY A FEW deleted records the whole thing of getting a deleted() bitmap (I/O) is of not helping anything in speeding up the query (See my previous test), because the whole process is overhead.

How long to you "think" this stuff is taking?

Your 1999 tests are not valid for binary indexes. Back on 11/10 you told Thomas you'd be posting the results of new tests and I've not seen any numbers posted.

> Whether or not it has been getting faster (with the binary index) is not the point:

I on the other hand think it is the whole point.

>It still is overhead.

more on your neglected overhead later in the reply...

>If I'm querying for a PK Value in a million record table, it essentially only need to examine the PK index, getting one record. If you drag a DELETED() tag of a million nodes into the mix, whether or not it is going to filter out the deleted index, is just overhead. The only index you needed was the PK index to have to fastest access in about all circumastances.

Since you diverge, I'll play the devils advocate here, if I was doing a one record SELECT I'd be inclined to just do an indexseek().

>Sure, but retrieving no DELETED() index file from what kind of I/O still is worse than no I/O at all, no matter what.

Yes and doing the I/O to retrieve the binary index will pretty quickly outperform the addtional record I/O you are causing to happen.

Bottom line removing I/O has a lot better performance gain than a few microseconds it takes to build a bitmap and AND it.

>Note that the 18 times faster is not a constant factor. In my tests it was about 5 times or so, while in certain cases it could be slower than a traditional DELETED() tag.

What tests are those? Must be with uninterestingly small tables.

N records, regular index size, binary index size, ratio of regular index size to binary index size

      10,     3072,    3072, 1
     100,     3072,    3072, 1
    1000,     5632,    3072, 0.546
   10000,    24756,    4608, 0.186
  100000,   307712,   16384, 0.053
 1000000,  3205632,  135168, 0.042
10000000, 38513152, 1314816, 0.034

At 100,000 rows the difference is a factor of 18, as the tables get bigger the difference become greater. At 10,000,000 the difference is a factor of 33 times. So yes in pre-VFP9 versions the cost to bring a 38mb index across the wire is quite high, the cost to bring 1mb in VFP9 is much less.

>It beats me. Maybe because they have been requested by some people who do not see the insanity of it. Go over the SQL Server forum and ask about always using a low selective index. You might get a different picture.

Again I'm not saying always use a low selectivity index. I am saying use indexes that improve performance within the confines of a particular application.

You are fixated on "micro" selectivity of the index, and that's just not always the case.

I am talking about reducing I/O. If using a binary index can help reduce actual record I/O it's a good thing. You also neglect the "overhead" of removing the excess records from the final result set.

>There unfortunately is more insanity. The whole identity thing is regarded as one of the biggest mistakes in DBMS history. Again go over to the SQL server forum and ask about it. It directly violates the releational model and is a pain in the b*tt in many cases. snip native key generator would have been far better.

ummmm totally irrelevant to this discussion.

>No the discussion is about no deleted record

set deleted off

> or a few deleted records.

the whole world is not "few" deleted records.

Dmitry did not qualify what percentage of deleted records he is dealing with.

>And the discussion is even more complicated because we are actually talking about NO or FEW deleted record in the rushmore optimizable bitmap. If I'm requesting a certain group of data which is optimizable and this group does not contain any deleted records, adding in a DELETED() tag is overhead, even if the table on the whole containes quite significant numbers of deleted records.

Again it is the difference between zero selectivity and micro selectivity and low selectivity and any other level of selectivity. If the selectivity is enough to reduce overall I/O then there is a gain. You advocate the retrieval of a 3000 byte record to get the one byte of deleted information I say there is a high probability that pulling 1 bit of data from a binary index tag can be done a lot faster. For example if the record size is 3000 bytes and the table size is 100,000 rows. It only takes the binary index to eliminate 6 records ( 16384 / 3000 ) before there is a net reduction in total I/O. 6 rows out of 100,000 is pretty low on the selectivity scale.

>Well we know that story: It is left to the individual developer. The fact is that the average developer does not have a clue what he is testing and it is by no means representative to the actual production environment. The threads about this topic in the past is just a plain prove of that.

So hopefully threads like this can educate those developers that don't do adequate performance testing.

>Then I don't understand why you come to different conclusions that I do. Please react on my testing report.

The 1999 tests? Or other tests (please point me at a message number if you don't mind) that have been done with a version of Europa that is not yet out of beta test? Any tests of this nature in Europa would need to be reevaluated once VFP9 ships.

FWIW since it's the Thanksgiving holidays here it may take a while before I can get back to this thread.