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

My code was only a suggestion.

The 2 extras? Seems that they are needed to figure out the table structure. Try the UDF with something simple like.

SELECT t1 FROM tmpfilter WHERE MyDf()

Try this on an empty tmpfilter too.

I've played around with CA's InsertCmdRefresh* properties as I figured out this one.

Agnes