Cristof,

>>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.

I real world applications, the 3NF sucks. I don't even want to prove this statement as there are plenty of profesional articles (at least in dutch) which say this.

>>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*

Yes it 'll give you some information: if the count in the stock table is not equal to the count in the articles table it indicates that a bug or crash occurred. from this you may can resolve the problem.

As for the performance matter a single table query is faster than a multiple table query for this matter.

>>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.

My intention was to tell how the optimizer works (in general) and was telling a theory. The samples were brought in by other people. To test my theory i did some testing with real-world data which i cannot send (for the same reason) through the internet.

>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.

Note that RECCOUNT is also a form of redundancy (like the stock field in the articles table). I rarely want to know the exact number of records.

If i have a process which scan a table a processes record by record i'll use a stratgy like this:

nRec=RECCOUNT()
SCAN FOR whatever
......
nProgress=RECCOUNT/RECNO() * 100
ENDSCAN

note that this will only work is no index order is 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).

I still don't see much use of what you're trying to tell me.

>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.

In fact mostly they won't need it.

>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.

Like i said to markus,

The deleted feature is in fact only a field in the table. If we don't add an index to all fields in the table because some indexes on those fields might not
be of any (or only a very small) benefit to performance, why should you add the deleted tag ?

I tend to use as less indexes as possible, which improves replacements and appending data. Having more indexes could lead to more index corruptions (you cannot corrupt indexes which don't exist), and as we all know, some kind of corruption is very hard to detect.

Only in cases where performance is a really important issue, i'll take the proper steps to enhance performance by adding an index (might be an index on deleted()).

I'm saying here that we must choose our indexes carefully. This is far more effective than just adding just an index because the optimizer may use it sometime.

Walter,