>Hi Kevin
>
>In VFP 9 this works, but not in VFP8
>
>UPDATE LineTemp ;
> SET NumCleared = (select count(*) from Details where BoIDBase = LineTemp.BoIDBase and RecType = "A" and AirTime#" " and AirDate BETWEEN dStartDate and dEndDate)
>

Hi Mike,

this is an interesting example.

Here, if you add an index on BoIdBase for optimize the join,
with the projection subquery VFP can run more slowly,
because the VFP engine is not very smart
( the correct plan is that of build a intermediate group subquery on the join fields
and then join it with the update table )

SYS(3054,0)

CREATE cursor LineTemp (BoIdBase i,NumCleared  i,NumCleared1 I   )
FOR k=1 to 1000
	INSERT into LineTemp values (RECCOUNT(),0,0)
NEXT

CLEAR

CREATE CURSOR Details (BoIdBase i,RecType  c, AirDate d , AirTime i)
FOR k=1 to 500000
	INSERT into Details values (RECCOUNT('LineTemp')*RAND(),IIF(RAND()>0.5,'A','B'),DATE()+RAND()*100,RAND()*3)
NEXT

	INDEX ON AirDate  tag t3
	INDEX ON RecType  tag t1
	INDEX ON AirTime  tag t2


dStartDate = DATE()+RAND()*10
dEndDate = dStartDate +RAND()*50

? "WITHOUT A JOIN INDEX"

TEST()

? "WITH A JOIN INDEX"
SELECT Details 
	INDEX ON BoIdBase tag tb
TEST()

PROCEDURE TEST

T1=SECONDS()
UPDATE LineTemp SET NumCleared = (SELECT COUNT(*) FROM Details D WHERE D.BoIdBase = LineTemp.BoIdBase AND D.RecType = "A" AND;
       NOT D.AirTime=0 AND D.AirDate BETWEEN m.dStartDate AND m.dEndDate)
? SECONDS()-T1

T1=SECONDS()
UPDATE LineTemp SET NumCleared1 = D.NumCleared  FROM ;
  (SELECT BoIdBase,COUNT(*) NumCleared  FROM Details;
	WHERE RecType = "A" AND NOT AirTime=0 AND AirDate BETWEEN m.dStartDate AND m.dEndDate GROUP BY 1) D ;
  WHERE D.BoIdBase = LineTemp.BoIdBase
  ? SECONDS()-T1