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.


>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
>