Hi greg,

>That's interesting because AccPac Pro Series has deleted() indexes on practically every table. Could it be because many installations never pack out tables for months and months on end?

Using indexes on deleted() that would speed up queries with huge performance gains has been a myth for many, many years. Actually in a production environment in tables with not containing huge amounts of deleted records it is only a burden to performance. Esspecially on slower networks to performance could get really bad for large tables as whole index tags need to be dragged accross the wire.

It is just the way rushmore works: It uses any usable index to speed up queries with out weighing the costs. Full optimization therefore often is NOT the most efficient way. If in a query you use a highly selective index to optimize any other less selective index is just overhead in the optimizing process.

There have been a number of threads in the last couple of years regarding this subject and in 1999 there was a very eleborate discussion to this matter Is it reasonable to have index on DELETED()? Thread #196021 Message #196021 and INDEX TAG on DELETED() ......... HUMBUG Thread #198031 Message #198031

Walter,

>I'm definitely not disagreeing with your advice, I just want to think about it before ripping all the Deleted() indexes out of Pro Series.
>
>Greg
>
>>Hi martin,
>>
>>>Just wanted to point out that my own experience showed me that it is not plain record count what slow things down, but having too many deleted records. The delay at USEing the table can be prevented with proper indexes on deleted(), as it is caused basically because a scan is needed to find the first non-deleted record.
>>
>>A very, VERY dangerous advice. an INDEX ON DELETED() will cause you headaches on large tables since for every rushmore optimizable expression about the whole index tag needs to travel the network before even finding one record. A simple browse statement can take several seconds to execute esspecially on slower networks.
>>
>>A simple USE could take several seconds as well (though I could not quickly replicate this behaviour in VFP8). It is way better to ensure that no deleted records exists at the top of your table (depending on the index order you set initially with the use command).
>>
>>An index on deleted() almost never is a good idea. This is a proven fact and there even is an article in Advisor handling this specific problem. So please, please remember: Throw away your indexes on deleted()...
>>
>>Walter,