Hi Dragan,

comments interspersed. . .
>>While your response is not in dispute (from here anyway) at all, I still find myself to be a minority of 1 in worrying about WHEN (and WHY) this (may have) changed. Personally, I'm still convinced that it WAS changed.
>
>I think it's only our perception of the matter that has changed. Those who knew that tag on deleted() was always good were thought to be gurus... and nobody questioned this. To add, the sys(1031) or whatever was the exact name of the "show me the optimization" never said "full" without it, which added to the belief. Nowadays, we're all a couple of years older, and hopefully wiser.
>
Older and wiser to be sure. BUT... how does that explain all of the reports of vastly improved performance after making a TAG on DELETED()??? This surely wasn't a figment of everyone's imagination. Here's an excerpt from Hacker's Guide:
----- start excerpt

But I Didn't Delete Any Records!
One of the optimization tips that fools lots of people has to do with SET DELETED. The typical conversation goes something like this:

"I have a query that's taking too long. How can I speed it up?"

"Create a tag on DELETED() for each table, if you have SET DELETED ON."

"But there are only a few deleted records. That shouldn't make much difference."

"Try it anyway."

(Later)

"You're right. It's much faster now. But there are only a few deleted records. How come it matters so much?"

What's going on here? In fact, you'll see the same speed-up even with NO deleted records. It's the setting of DELETED that matters.

Here's the point. Even in many complex queries and FOR clauses, Rushmore performs its magic almost entirely on the relatively small and compact CDX file, a file structured with nodes, branches and leaves to be searched efficiently. When DELETED is ON, FoxPro has to check each and every record in a result set (whether from a query, a filter, or FOR) to see if it's deleted—even if no records are actually deleted. This sequential reading of the entire cursor or file completely defeats the benefits of Rushmore. Don't do it!

By creating a tag on DELETED(), you let Rushmore do the checking instead of looking at each record sequentially, which makes the whole thing much faster. The larger the result set, the more speed-up you'll see.

---- end excerpt
See the "You're right. It's much faster now." in the excerpt? This was a most common response, even here on UT. So why is it different now (even on a 'straight' SQL query)???

>>The MSDN (??) Article of Oct.96 titled "Performance Tuning Tips for MS VFP", the Hacker's Guide and others that I cannot cite at this moment all state directly and without reservation that an index on DELETED() will improve performance (I will grant that DELETED ON is stated or implied) of any SQL query.
>>FPA Tips and articles have mentioned this too (until THAT article) and a whole lot of people here on UT, on being told to do so after complaining about poor performance have reported back that 'things now are flying'.
>
>This narrows down to the question that if Set("deleted")="ON", do SQL queries have to read the table physically to check for deletion marks? Do they have to do it each time? Or do they do it only over the result set? Seemed to be that having a tag on Deleted() would help, and it probably did in many cases - but then, the rule is not sacred anymore. Exceptions have been found... so, OK, one deity less.
>
>>I wish that some satisfactory explanation could be discovered for this.
>
>I just want to know does VFP's SQL read the deletion mark of all the records, or only the retrieved records. That would make a difference. BTW, how come nobody mentioned this tag in 2.x? Not that I can remeber.

Well I don't know. The Hacker's Guide suggests that it does read each. I know from experience (FPW 2.6) that I had a two field table with an index on a single field and when I accessed that table using the index and only referencing the key field in the logic it still went to the physical records.

I'm still very suspicious about the current situation - how could so many people (guru and 'normal') SEE an improvement then. How would they actually know that performance is now degraded with current VFP (in the general case)???

Cheers,

Jim N