Hi Agnes,
>Sorin,
>the subselect will be evaluated over and over. In your example for every record in the outer select. So there is a bit of work to do.
AFAIK, that is not correct. The inner select is only executed once and placed in a temp file. Then for each record in the outer select it is searching the temp file for a match. The optimizer in the first approach just does do the opposite: Take each value in the IN clause and search that in the table directly using the index.
AFAICS, the optimizer is not smart enough to see that both cases are equivalent. SQL server might be a bit smarter in this respect.
Walter,
>>Can someone please explain why
>>When querying a VFP free table, using a
sub-select filter is 500 times slower than using a string
list of comma separated values.
>>*
>>ANSI is OFF, EXACT is OFF, ENGINEBHAVIOR 70. Though it doesn't seem to make a difference otherwise
>>To illustrate, please run the following code in a prg:
>>*
>>
LOCAL i, lcList, lcQval, nSec, lcCmd
>>* Create source data cursor
>>CREATE CURSOR tmpSource (fs1 C(10), fs2 C(10))
>>FOR i = 1 TO 1000000
>> INSERT INTO tmpSource (fs1, fs2) ;
>> VALUES (;
>> 'F1' + PADR(TRANSFORM(m.i), 8, "0"), ;
>> 'F2' + PADR(TRANSFORM(m.i), 8, "0"))
>>ENDFOR
>>INDEX ON fs1 TAG fs1
>>*
>>* Create and populate cursor and list of explicit query values
>>lcStrList = ""
>>CREATE CURSOR tmpFilter (t1 C(10))
>>FOR i = 1 TO 10
>> lcQval = 'F1' + PADR(TRANSFORM(m.i * 100), 8, "0")
>> * Populate cursor for sub-select
>> INSERT INTO tmpFilter (t1) VALUES (m.lcQval)
>> * Build list of explicit values
>> lcStrList = m.lcStrList + ["] + m.lcQval + [",]
>>ENDFOR
>>lcStrList = RTRIM(m.lcStrList, 1, ",")
>>*
>>* Now, the test
>>* From explicit list of (comma separated) values
>>nSec = SECONDS()
>>lcCmd = "SELECT * FROM tmpSource WHERE fs1 IN (" + m.lcStrList + ") INTO CURSOR tmp"
>>&lcCmd
>>? TRANSFORM(SECONDS() - m.nsec) + " seconds to select " + TRANSFORM(RECCOUNT()) + " records - From (list)"
>>*
>>* From sub-select
>>nSec = SECONDS()
>>SELECT * FROM tmpSource WHERE fs1 IN (SELECT t1 FROM tmpFilter) INTO CURSOR tmp
>>? TRANSFORM(SECONDS() - m.nsec) + " seconds to select " + TRANSFORM(RECCOUNT()) + " records - From Sub-select"
>>*
>>USE IN tmp
>>USE IN tmpSource
>>USE IN tmpFilter
>>
