Hi Walter,

>In most of my cases i just add a field in articles which holds the stock value.

Many people try to design their tables to meet 3NF and that requires that you don't have redundant fields in your tables (beside a lot of other things). Also, managing this extra field takes a lot of time and might even cause ambiguos results.

>This does not only speed up performance but also gives you some extra info if the app crashes or a bug occurs.

No, it doesn't give you any extra information. It just tells you that the stock table and the article table are out of sync and either of them is wrong. And in order to speed up the query, you can still create an index tag. *g*

>I did not insist anything. I only said that filtered results cannot be compared with real cursors. With filtered result the query doesn't actually execute, it merely reopens the table and sets a filter.

It does execute the query, it must execute the query in order to set _TALLY and determine the number of records. All it doesn't do is creating a physical table. Because it's so fast with an index on DELETED(), you might think that it doesn't execute the query. But you are right, a query that creates a filtered view and one that creates a physical cursor cannot be compared.

>100% prove is very hard to get. You could prove i'm wrong if you have a real- world example which shows all of us that in this case it reasonable to use the tag.

Well, should I send you about 700 MB through the internet??? The problem is, we can't compare real-world applications here. But that doesn't mean, we can use samples to draw a conclusion how it would work in all other cases.

>I certainly did test this with SQL SELECT with group by, having ,joins and subselects.

Well, but you didn't show us these test cases.

>>Clear All
>>Close ALl
>>Release ALl
>
>This doesn't clear the local cache.

Right, but it closes the tables. That's all I want to do here.

>True. If you read the other thread, i did mention this as one exception.

In that case, sorry.

>I don't about you, but i rarely issue a count to ... command. Often it is reasonable to use reccount.

RecCount() doesn't tell you anything for a table, since it includes deleted records as well as non-deleted records. In fact, all it does is to read the header of a table. You only get meaningfull results if you issue it on a physical cursor, but that's too late for me. I want to know the number of records before I lock up the users machine for a minute or more by downloading a large result set.

>If the condition returns a small subset it may not be a problem to do this without full optimization.

Exactly, and in order to determine whether it is a small result set, I use count (or more precisely SELECT CNT).

>My application don't have this feature as it takes up valuable time to count (even if it's fully optimizable).

Usually this indicates that the query is not fully optimized.

>Untill now, i've really got the feeling that these arguments you're comming up are fairly exceptional. Most of these argument don't apply to any of my applications.

They apply to my applications and they apply to applications from others here that might get the impression that they don't need such a tag because in your applications you don't need such tag.

>From various tests i've run i couldn't find a convincing evidence in favour of the deleted tag (except for the given exceptions).

But how many evidences you found in favour of omitting the index tag? If there's hardly no speed difference, I would always vote in favour of a tag, because it might be usefull in further queries. Only if it actually slows down the application signifcantly , I'd omit it obviously. And I've omitted it in my applications in these situations.

>If your point is: Hey walter, an index on deleted() could be handy, I certainly agree. But if it is: Hey walter, how could you make such a stupid statement , You alway's should add an index on deleted() I really think you're wrong.

My point is: Add an index tag where it makes your application faster or doesn't slow it down, omit it where it slows down your application. Your point seems to be: Add an index when it makes your application significantly faster, omit it in all other cases.

Christof