Hi Nadya,

>The results are 122,218 sec. for set deleted on and 37.996 sec. for set deleted off. Still the speed is really slow :(((((( We have to investigate the problem with this server. The resulting number of records was the same.

This, is the proof that the DELETED() tag is killing your performance. If you can get rid of the DELETED() tags, the query will take about 38 seconds. O.K. still not fast, but a lot better than 122 seconds.

Further, because now the server and network loads will also decrease because less data has to be send to the client. The workstations do not run out of memory that easy because the information of the deleted() are not kept in memory (about 35 Mb in your case). I'll not be surprised that if you can get rid of those index tags overal systems performance will get even better.

General rule: Don't use DELETED() tags, and esspecially not on large tables


>I re-run it again and got 47,4 sec. for first case and 35.573 for the second.

This is because your local workstation was able to cache some data, because you are the only user who opened one or more tables (optimistic locking mechanism on the server)

Walter,