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 >I got the same results even with index added. Inner JOIN is the fastest, but it produces different result:
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 * browse * 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 INDEX on t1 TAG t1 lcStrList = RTRIM(m.lcStrList, 1, ",") * BROWSE * 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 _cliptext = 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 nofilter _cliptext = _cliptext + CHR(13) + CHR(10) + TRANSFORM(SECONDS() - m.nsec) + " seconds to select " + TRANSFORM(RECCOUNT()) + " records - From Sub-select" * * From exists nSec = SECONDS() SELECT * FROM tmpSource WHERE exists (SELECT 1 FROM tmpFilter WHERE t1 = tmpSource.fs1) INTO CURSOR tmp nofilter _cliptext = _cliptext + CHR(13) + CHR(10) + TRANSFORM(SECONDS() - m.nsec) + " seconds to select " + TRANSFORM(RECCOUNT()) + " records - using Exist" * * Inner Join nSec = SECONDS() SELECT tmpSource.* FROM tmpSource INNER join tmpFilter ON tmpSource.fs1 = tmpFilter.t1 INTO CURSOR tmp nofilter _cliptext = _cliptext + CHR(13) + CHR(10) + TRANSFORM(SECONDS() - m.nsec) + " seconds to select " + TRANSFORM(RECCOUNT()) + " records - using INNER JOIN" USE IN tmp USE IN tmpSource USE IN tmpFilter---------------------------------
0.001 seconds to select 55 records - From (list) 0.402 seconds to select 55 records - From Sub-select 0.398 seconds to select 55 records - using Exist 0 seconds to select 62 records - using INNER JOIN