Hi nadya,

>>Yeah, Jim alerted me to that. I thought I had checked all the links when I moved the material from esolserv to my new host..and FrontPage didn't complain about a broken link. It's back up now.
>
>
>Thanks, Craig.
>
>From your article: Deleted Records
>How you handle deleted records can have a huge affect on Rushmore speed, especially if you have a very large table with lots of deleted records. Creating an index like INDEX ON DELETED() TAG Del and then issuing SET DELETED OFF will make your queries run faster.
>
>I think, you meant SET DELETED ON here.

Awwwhhh man, this is simply wrong. An index on DELETED() TAG del is killing for rushmore performance on large tables. And of course, SET DELETE OFF is speeding up queries, because then rushmore does not use the DELETED() Tag.

>Also from your article So, it may take longer to actually build the index and run the query than to just run the query. My advice is, unless you have a HUGE amount of data, to not build the index and let Rushmore determine if one is needed.

This applies to indexing intermediate results between two SQL SELECT queries, and does not have anything to do with the DELETED() Tag.

>SiteMstr has ~2mln.records, TranMstr has about 5mln. and CMRView has ~30 rec.

With these amounts the DELETED() tags are most likely the cause of the problems. It's because the way rushmore works, the whole index tag for each table has to be loaded into memory beforce before the actual query can be executed. An (logical) indexnode for deleted() contains 1 byte for the locical value and 4 bytes for the record pointer =5 bytes (discarding the overhead of the physical indexnodes). Well, if you've got 5mln+2mln records, this means that before executing the query 7mln * 5 bytes (=35 Mb !!!) have to be loaded into memory before the actual query can be executed.

Just to test if this is causing your performance problems, just set SET DELETED OFF and run the same query. It probably runs much faster because it now does not load the DELETED() tags.

Walter,

>Unfortunately, TranMstr doesn't have a town field, so I must join to SiteMstr (use only primary address - PrefCode='P') to then filter particular towns.
>I'm thinking about doing TranMstr+SiteMstr first (would result ~100000 rec.), then index it on town, then do the last join to CMRView. What do you think?