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,


>>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+extrWeekprefcode='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+extrWeekprefcode='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+extrWeekbetween(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+extrWeekbetween(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