>Hi nadya,
>
>> I will try. Unfortunately, it was not my decision to include deleted tag in each of our table. I doubt, that I can influence to change it. Also we found lots of other problems with Development server. It's running all queries very slowly, even for the single table.
>
>Well Try the following to prove my statement:
>Take the 5 mln table (with a DELETED() tag) and construct a SQL SELECT that should only return one record in which the where clause is based on the primary key (if possible):
>

SELECT * FROM MyLageTable WHERE My_Pk = MyValue INTO CURSOR Result NOFILTER

>
>Now try it with SET DELETE ON and with SET DELETE OFF.
>
>The first takes a long time to execute, the second should execute almost instantly.
>
>Walter,

Hi Walter,

I ran couple of tests and if I make an SQL from a single table, you're right. When I joined two tables, queries with SET DELETED OFF run 1,5sec. slowly than SET DELETED ON.
before dropping deleted tag
This is the resulting text:

Set deleted on full query 23 sec.
Set deleted off full query 24.24199999999837 sec.
Set deleted on query without extra join 3.697000000000116 sec.
Set deleted off query without extra join 0.849000000001979 sec.
************************************************************
Set deleted on full query 23.3179999999993 sec.
Set deleted off full query 24.35899999999674 sec.
Set deleted on query without extra join 3.697999999996682 sec.
Set deleted off query without extra join 0.841999999996915 sec.
************************************************************

after dropping deleted tag

************************************************************
Set deleted on full query 19.75500000000466 sec.
Set deleted off full query 22.99299999999494 sec.
Set deleted on query without extra join 2.838999999999942 sec.
Set deleted off query without extra join 0.832999999998719 sec.
************************************************************
Set deleted on full query 19.99199999999837 sec.
Set deleted off full query 22.94000000000233 sec.
Set deleted on query without extra join 2.86699999999837 sec.
Set deleted off query without extra join 0.819999999999709 sec.
************************************************************
Set deleted on full query 19.95300000000134 sec.
Set deleted off full query 23.01299999999901 sec.
Set deleted on query without extra join 2.826000000000931 sec.
Set deleted off query without extra join 0.834000000002561 sec.
************************************************************
Set deleted on full query 19.94000000000233 sec.
Set deleted off full query 22.91700000000128 sec.
Set deleted on query without extra join 2.822000000000116 sec.
Set deleted off query without extra join 0.835999999995693 sec.
************************************************************

As you can see, in both cases set deleted off slowed the query in case of two tables and increased the speed for a single table. Strange...

and this is a program:

close all
clear
sys(3054,11)
local lnSec, lcStr, CR
CR=chr(13)
set talk on
set deleted on
lnSec=seconds()
open data CT
lcStr=''
SELECT "CT" as State, SiteMstr.ccode, SiteMstr.town,  ;
Date, Mortgage, Source, Lender, extrWeek, extrYear ;
FROM TranMstr  ;
Inner Join SiteMstr on TranMstr.PropID=SiteMstr.PropID ;
where between(mortgage,25000,500000) and extrYear+extrWeek<='200121' and ;
prefcode='P' and between(date,{^2001-01-01},{^2001-05-30})  INTO cursor curTest1
?seconds()-m.lnSec, _tally
lcStr=m.lcStr+'Set deleted on full query '+ transform(seconds()-m.lnSec)+' sec.'+m.CR

set deleted off
lnSec=seconds()
open data CT
SELECT "CT" as State, SiteMstr.ccode, SiteMstr.town,  ;
Date, Mortgage, Source, Lender, extrWeek, extrYear ;
FROM TranMstr  ;
Inner Join SiteMstr on TranMstr.PropID=SiteMstr.PropID ;
where between(mortgage,25000,500000) and extrYear+extrWeek<='200121' and ;
prefcode='P' and between(date,{^2001-01-01},{^2001-05-30})  INTO cursor curTest1
?seconds()-m.lnSec, _tally
lcStr=m.lcStr+'Set deleted off full query '+ transform(seconds()-m.lnSec)+' sec.'+m.CR

set deleted on
lnSec=seconds()
SELECT "CT" as State, ccode, town,  ;
Date, Mortgage, Source, Lender, extrWeek, extrYear ;
FROM TranMstr  ;
where between(mortgage,25000,500000) and extrYear+extrWeek<='200121' and ;
between(date,{^2001-01-01},{^2001-05-30})  INTO cursor curTest1
?seconds()-m.lnSec, _tally
lcStr=m.lcStr+'Set deleted on query without extra join '+ transform(seconds()-m.lnSec)+' sec.'+m.CR

set deleted off
lnSec=seconds()
open data CT
SELECT "CT" as State, ccode, town,  ;
Date, Mortgage, Source, Lender, extrWeek, extrYear ;
FROM TranMstr  ;
where between(mortgage,25000,500000) and extrYear+extrWeek<='200121' and ;
between(date,{^2001-01-01},{^2001-05-30})  INTO cursor curTest1
?seconds()-m.lnSec, _tally
lcStr=m.lcStr+'Set deleted off query without extra join '+ transform(seconds()-m.lnSec)+' sec.'+ m.CR+replicate('*',60)+m.CR
strtofile(m.lcStr,'test.txt',.t.)
modi file test.txt