Walter,

>>Even if it has significant amounts of deleted records (say 10%) the netto result of having a DELETED() tag is insignificant. When going over a 50% it is getting benificial.

>You seem to fail to understand the relative time differences between memory access time, local disk access time and network file access time. There are orders of magnitude of time difference between these three items.

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. Whether or not it has been getting faster (with the binary index) is not the point: It still is overhead.

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.

>In a very rough test I could retrieve 6 megabytes per second across a 100 Mb network. I could retrieve 26 megabytes per second off my local hard drive and 130 megabytes per second from memory.

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.

>The fact that a binary index can be brought across the network wire about 18 times faster is quite significant because that is the slowest operation being performed. The time to construct a bitmap and then AND it into the current result is quite a bit faster compared to the retrieval time of the actual data rows across the wire that could have been eliminated via the Rushmore bitmap. The current microprocessor instruction sets have single commands that will AND two blocks of memory together so they fundamentally work at memory access speed.

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.

>Ask yourself the question why would Microsoft even bother adding this binary functionality to the CDX if there were no benefits to it?

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.

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.

When the features of VFP8 became know I already made a statment why it is not a good idea. Re: News scoops from EssentialFox Thread #649984 Message #655411. This was long before the release of VFP8. Of course I was right, because people started using it and exactly faced the problem I outlined, requiring a fix to this problem with VFP9. Though it adresses part of the problem, another is still left there. Again, newbees (and even experienced developers) are forced into trouble. If they had no choice than to use a PK generator that uses a table of PK values independed of the field beeing generated things would not have been that bad. A solution like in oracle where you have a native key generator would have been far better.

>>You avoid the real issue here. If you want to take part of a constructive discussion then please leave this kind of bogus arguments at home.
>
>You are the one that keeps introducing "no deleted" records into the discussion not me. I was merely pointing out if that truly is the case SET DELETED OFF is an available option. You are quite free to treat that as a specious argument if that's how you want to treat it.

No the discussion is about no deleted record or a few deleted records. 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 you don't seem to understand that with very few or no deleted record there is from a logical point of view NO WAY using an index on deleted() is speeding up the query. The index does simply not weed out any records, so processing the index tag was useless and a waste of resources and time. And even if there are one or two records in the optimizable portion of the query, filtering out those without an index, is not going to be slower than downloading a whole index tag and processing it in a rushmore bitmap.

>It amounts to a tradeoff between how long it takes to retrieve the index, construct the bitmap and AND it, compared to retrieval time of a record that won't actually make it into the result set. This also depends a lot on the size of the row and the actual network performance. So I'll stand by my assertion that it should be left up to individual testing to determine if it helps performance. I'm not one to dismiss it out of hand because of a "feeling" that it won't help.

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.

>I for one do quite a bit of development and production testing in a true production environment. I'll just say that good developers have a decent handle on performance testing.

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

Walter,