Hi Walter,

Actually, I've realized, that these tests are not quite accurate (since I was running them from the first to the last), so I re-write the testperfomance program a little bit. I'm running these tests from my local machine, but all tables are on the server. Right now I'm the only one (I assume), who is working on this server.

Here the results so far (I already dropped the deleted tag):

Set deleted on full query 22.79699999999866 sec.
Set deleted on full query 25.24000000000524 sec.
Set deleted on full query 23.48200000000361 sec.
Set deleted on full query 25.34599999999773 sec.
Set deleted on full query 23.0570000000007 sec.
Set deleted on full query 22.5879999999961 sec.
Set deleted on full query 22.17400000000634 sec.
Set deleted on full query 23.85699999999633 sec.
Set deleted on full query 23.12199999999575 sec.
Set deleted on full query 22.49399999999878 sec.
Average query speed is 23.41569999999992
____________________________________________________________
Set deleted off full query 22.27700000000186 sec.
Set deleted off full query 24.8640000000014 sec.
Set deleted off full query 22.40000000000146 sec.
Set deleted off full query 21.22800000000279 sec.
Set deleted off full query 23.87900000000081 sec.
Set deleted off full query 22.54100000000471 sec.
Set deleted off full query 21.64699999999721 sec.
Set deleted off full query 22.44900000000052 sec.
Set deleted off full query 22.93800000000192 sec.
Set deleted off full query 22.10399999999936 sec.
Average query speed is 22.6327000000012
Set deleted on query on just one table 3.315999999998894 sec.
Set deleted on query on just one table 0.862999999997555 sec.
Set deleted on query on just one table 0.855999999999767 sec.
Set deleted on query on just one table 0.86699999999837 sec.
Set deleted on query on just one table 3.246999999995751 sec.
Set deleted on query on just one table 0.85899999999674 sec.
Set deleted on query on just one table 0.881000000001222 sec.
Set deleted on query on just one table 0.88300000000163 sec.
Set deleted on query on just one table 2.802000000003318 sec.
Set deleted on query on just one table 0.899000000004889 sec.
Average query speed is 1.547299999999814
____________________________________________________________
Set deleted off query on just one table 3.387000000002445 sec.
Set deleted off query on just one table 0.789000000004307 sec.
Set deleted off query on just one table 0.792000000001281 sec.
Set deleted off query on just one table 0.789999999993597 sec.
Set deleted off query on just one table 3.176999999996042 sec.
Set deleted off query on just one table 0.781999999999243 sec.
Set deleted off query on just one table 0.802000000003318 sec.
Set deleted off query on just one table 0.807000000000699 sec.
Set deleted off query on just one table 2.730999999999767 sec.
Set deleted off query on just one table 0.815000000002328 sec.
Average query speed is 1.487200000000303
____________________________________________________________

For each test I re-started VFP.

Here is the test program

lparameter tcDeleted, tnJoins
close all
clear
sys(3054,11)
local lnSec, lcStr, CR, i, lnEnd, lnTotal
CR=chr(13)
lnTotal=0
set talk on
if empty(m.tcDeleted)
	set deleted on
else
	set deleted off
endif
open data g:\redp\dbc\ct\ct
lcStr=''

for i=1 to 10
	lnSec=seconds()
	if empty(m.tnJoins)
		select "CT" as State, SiteMstr.ccode, SiteMstr.town,  ;
			date, Mortgage, source, Lender, extrWeek, extrYear ;
			from TranMstr  ;
			Inner join SiteMstr on TranMstr.SiteID=SiteMstr.SiteID ;
			where between(Mortgage,25000,500000) and extrYear+extrWeek<='200121' and ;
			between(date,{^2001-01-01},{^2001-05-30})  into cursor curTest1
	else
		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
	endif
	lnEnd=seconds()
	?m.lnEnd-m.lnSec, _tally
	do case
	case empty(m.tcDeleted) and empty(m.tnJoins)
		lcStr=m.lcStr+'Set deleted on full query '+ transform(m.lnEnd-m.lnSec)+' sec.'+m.CR
	case !empty(m.tcDeleted) and empty(m.tnJoins)
		lcStr=m.lcStr+'Set deleted off full query '+ transform(m.lnEnd-m.lnSec)+' sec.'+m.CR
	case empty(m.tcDeleted) and !empty(m.tnJoins)
		lcStr=m.lcStr+'Set deleted on query on just one table '+ transform(m.lnEnd-m.lnSec)+' sec.'+m.CR
	case !empty(m.tcDeleted) and !empty(m.tnJoins)
		lcStr=m.lcStr+'Set deleted off query on just one table '+ transform(m.lnEnd-m.lnSec)+' sec.'+m.CR
	endcase
	lnTotal=m.lnTotal+(m.lnEnd-m.lnSec)
	use in select('curTest11')
endfor
lcStr=m.lcStr+"Average query speed is "+ transform(m.lnTotal/10)+ m.CR + replicate('_',60)+m.CR

strtofile(m.lcStr,'test.txt',.t.)
modi file test.txt

>Hi nadya,
>
>I guess you've got some number of deleted records in one or either tables. From the test results I get the impression that you ran this on a local workstation.
>
>When you run this trough a network while some other workstation is accessing one of those files (for write) you'll get other numbers: the deleted() tag would slowdown your query significant because the whole deleted() index tag for both tables get dragged accross the network. Maybe not that significant if you're running on a 100 MB network or higher, but a real pain on a stressed 10 Mb network. On your local workstation the bandwidth (between HD and memory) is not a problem, so one easely draws the conclusion that the DELETED() tag speeds up things.
>
>The odd results you're getting are most likely due to the internal buffering VFP and the OS uses. You can get more reliable results if you run this thing accros a network while some other workstation has opened the tables also.
>
>Good luck,
>
>Walter,
>
>