>Andrus,
>
>A tag on deleted() does indeed affect performance. I don't just say this because "I've heard others say it, so I better do it too". I do it because I have tested it and have seen the adverse effects of not having it. It is a plain and simple fact that IF set deleted is ON the SELECT statement must read the table to check the deleted status of the records. If your table is "wide" this means a lot of disk I/O. If the deleted tag exists it can read a much smaller chunk of data from the cdx file.
>
>There are a lot of other factors about what makes rushmore work. If sys(3054,11) is telling you it's only getting partial optimization then you should figure out exactly why that is happening. There are lots of reasons that have nothing to do with a deleted tag that can cause only partial optimization.
>
>Other factors such as internal cdx storage fragmentation, network vs local drive, memory use, disk cache, disk fragmentation, what other programs are running at the time, etc. All have an influence on the result. The test cases you have posted are not real world because of the data you are using.
>
>Here is a much better, more like real world data, test you can run:
>
>Run this program first:
>
>

set safety off
>
>create table deltest ( c1 c(20) )
>
>for i = 1 to 1000000
>   insert into deltest values ( str( rand(), 10, 8 ) )
>endfor
>
>index on c1 tag c1
>
><b>Then run this program 4 times:</b>
>
>* deltag1on
>clear
>close data all
>
>use deltest in 0
>
>set deleted off
>
>lnStart = seconds()
>
>for i = 0.0 to 0.9 step 0.1
>  lcMin = str( i, 10, 8 )
>  lcMax = str( i+0.1, 10, 8 )
>   select * ;
>      from deltest ;
>      into cursor x1 ;
>      where c1 >= m.lcMin and c1 < m.lcMax
>endfor
>
>? seconds() - lnStart
>
><b>Now run this program 4 times:</b>
>
>* deltag1off.prg
>clear
>close data all
>
>use deltest in 0
>
>set deleted off
>
>lnStart = seconds()
>
>for i = 0.0 to 0.9 step 0.1
>  lcMin = str( i, 10, 8 )
>  lcMax = str( i+0.1, 10, 8 )
>   select * ;
>      from deltest ;
>      into cursor x1 ;
>      where c1 >= m.lcMin and c1 < m.lcMax
>endfor
>
>? seconds() - lnStart

>
>note the significant difference in time
>
>In the command window:
>
>index on deleted() tag deleted
>
>rerun each the last two programs 4 times and observe the performance improvement. For my P2-300 Dell notebook, 64 meg, Win98, VFP6, local drive. I get these results:
>
>Before the deleted tag exists:
>
>deltag1off: 0.559, 0.576, 0.553, 0.552
>deltag1on: 14.040, 13.275, 13.376, 13.360
>
>After the deleted tag is created:
>
>deltag1off: 0.555, 0.557, 0.554, 0.553
>deltag1on: 3.214, 2.421, 2.407, 2.417
>
>I consider a factor of 5 performance improvement pretty significant!
>
>Having a single tag doesn't so severely impact record insertions. If you are doing lots of bulk appends you should consider dropping all the tags, appending and rebuilding the tags. This will keep your cdx files internally optimized as well.
>
>>David, how can index on deleted() increase perfomance if this
>>program show the opposite: it even decreases perfomance a lot.
>>Can you point to any tests or sample program which shows that
>>index on deleted() increases perfomance when there are few
>>deleted records? Actually, rusmore on deleted() needs to run and
>>this wastes more time than using query without it.

Hello David!

I read your conversation, and i realized, that I went on Deleted() tag so easy. I will pay more attention it in future.
It is very reasonable to keep cdx files internally optimized. I guess you mean simmetric B trees.
Is reindex command enough or should tags be removed?

BB