>Hi Walter,
>
>o.k, this is hard to test for a simple SELECT like this one. Maybe I place a UDF in the colum section of the SQL and count.
>
>something like
>

>PUBLIC lnCount
>lnCount=0
>SELECT * FROM tmpSource WHERE fs1 IN (SELECT t1+MyUDF() FROM tmpFilter) INTO CURSOR tmp
>?lnCount


That does not run in my example, returning the SQL subquery is too complex.But consider the code below

<PRE> 

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 NOFILTER "
&lcCmd
? TRANSFORM(SECONDS() - m.nsec) + " seconds to select " + TRANSFORM(RECCOUNT()) + " records - From (list)"
*
* From sub-select
lnCount = 0

nSec = SECONDS()
SELECT * FROM tmpSource WHERE fs1 IN (SELECT t1 FROM tmpfilter WHERE MyDf(T1)) INTO CURSOR  tmp
? TRANSFORM(SECONDS() - m.nsec) + " seconds to select " + TRANSFORM(RECCOUNT()) + " records - From Sub-select"
*

lnCount = 0
nSec = SECONDS()
SELECT * FROM tmpSource WHERE fs1 IN (SELECT t1 FROM tmpfilter WHERE MyDf()) INTO CURSOR  tmp
? TRANSFORM(SECONDS() - m.nsec) + " seconds to select " + TRANSFORM(RECCOUNT()) + " records - From Sub-select"


? lnCount
USE IN tmp
USE IN tmpSource
** USE IN tmpFilter

function MyDF
LPARAMETERS x
lnCount=lnCount+1
return 
endfunc

In the first instance, when passing a field to the MyDf, it only runs 67 times. At this point I have no idea why it runs 67 times as there are only 10 records in tmpfilter. Perhaps it runs again for each found record (55). 55 + 10 = 65 + 2 extra times for unknown reasons = 67

The second instance when not passing a parameter, you're right it executes the number of records in tmp source+1. However, having no where clause in the original and the time is similar to the first example, I think the subselect would on run once or very few times, cetainly not for each row in the outer query.

Walter,