>>Patrick,
>>Queries with subqueries are expected to run slow. There was a discussion about this and showing why an "IN" query would be much slower on fox4um.com (but I'm afraid it's in Turkish).
>>I'd use an inner join or xbase.
>>Cetin
>
>Are you able to share the basic concept that was discussed of why an "IN" query would be expected to be slower in fox? It is not the case in other databases (MSSQL or DB2).
>
>Thanks.
It's not the case in MSSQL and DB2 true but they nicely optimize it running the IN query once. VFP runs it multiple times (not an exact count, depends and might be up to reccount()+1). In pseudocode it looks like:
select QueryCursor
scan
select Subquery into cursor subCursor
If Id is in subCursor
Add to result
endif
endscan
Here is a very simple test code:
Rand(-1)
Create Cursor test1 (Id i)
Create Cursor test2 (Id i, f1 c(1))
For ix=1 To 10
Insert Into test1 Values (m.ix)
For jx=1 To 5
Insert Into test2 Values (m.ix,Chr(Asc('A')+Rand()*26))
Endfor
Endfor
lnCalled = 0
Select * from test1 ;
where id in (select id from test2 where f1 == "H" and HowManyTimes())
? m.lnCalled
Function HowManyTimes
lnCalled = m.lnCalled + 1
Cetin