I have built a "filter control class". I drop instances of this control on a form - in this case 13 controls. Each control has a grid that contains one column from the cursor I am querying and one checkbox column. Then I allow the user to check or uncheck the values they want. From there I build a query as per the below. There is an index on every column in "curdataunfiltered". There is also an index on both columns for the "in clauses", i.e. the column and the linclude logical field.

I would think that I would get full rushmore optimization on this query. No? Can full optimization occur on a query of this form, i.e. with an "in clause". Would I be better off if I used and "exists" query.

The number of rows is not that large, about 6 thousand records in curdataunfiltered. The query, when all checkboxes are selected for all columns, executes in .04 seconds. But I want this to scale well for large data sets. Also the query creation is not that smart yet. I will enhance the class so that if all checkboxes are selected it will ignore that subquery, Even so, it needs to perform well even when all 13 subqueries need to be used.

lc_select = "select * from curdataunfiltered  where "
lc_where = ""
ln_len =  Alen(Thisform.filter_controls,1)
For i = 1 TO ln_len
		If Vartype(Thisform.filter_controls[i,1]) <> 'L'
			lc_i = Alltrim(Str(i))
			lc_recordsource = Strtran(This.Parent.column_filter&lc_i..ssbgrd1.RecordSource,"'","")
			lc_where = lc_where + ;
				this.Parent.column_filter&lc_i..ccol1controlsource + " in ( " +  "select "  + This.Parent.column_filter&lc_i..ccol1controlsource + ;
				" from " +   lc_recordsource + " where linclude ) "  
			*lc_where = lc_where + IIF(i<ln_len," AND ", "")
		ENDIF
		IF i < ln_len AND Vartype(Thisform.filter_controls[i+1,1]) <> 'L'
		
			lc_where=lc_where + " AND "
		ELSE
			exit	
		endif
ENDFOR
*SYS(3054 [, 0 | 1 | 11 | 2 | 12] [, cMEMVAR])
LOCAL lcrushmore
=SYS(3054,11,"lcrushmore")
lc_sql = lc_select + lc_where +  "into cursor curtempresult readwrite nofilter "
ln_seconds=SECONDS()
&lc_sql
ln_seconds2=SECONDS()
ln_time=ln_seconds2-ln_seconds
SET STEP ON 

this generates the following query:
select * from curdataunfiltered where ordnum in ( select ordnum from curdata_ordnum__36K0KWH8U where linclude )
AND accountnam in ( select accountnam from curdata_accountnam__36K0KWH9E where linclude )
AND orddesc in ( select orddesc from curdata_orddesc__36K0KWH9K where linclude )
AND zip in ( select zip from curdata_zip__36K0KWH9N where linclude )
AND geocode in ( select geocode from curdata_geocode__36K0KWH9R where linclude )
AND quantity in ( select quantity from curdata_quantity__36K0KWH9U where linclude )
AND rate in ( select rate from curdata_rate__36K0KWH9Y where linclude )
AND weight in ( select weight from curdata_weight__36K0KWHA2 where linclude )
AND versname in ( select versname from curdata_versname__36K0KWHA5 where linclude )
AND seller in ( select seller from curdata_seller__36K0KWHA9 where linclude )
AND carrier in ( select carrier from curdata_carrier__36K0KWHAC where linclude )
AND cmastname in ( select cmastname from curdata_cmastname__36K0KWHAF where linclude )
into cursor curtempresult readwrite nofilter



rushmore levels show:

Rushmore optimization level for intermediate result: none
Using index tag Linclude to rushmore optimize intermediate result
Rushmore optimization level for intermediate result: partial
Using index tag Linclude to rushmore optimize intermediate result
Rushmore optimization level for intermediate result: partial
Using index tag Linclude to rushmore optimize intermediate result
Rushmore optimization level for intermediate result: partial
Using index tag Linclude to rushmore optimize intermediate result
Rushmore optimization level for intermediate result: partial
Using index tag Linclude to rushmore optimize intermediate result
Rushmore optimization level for intermediate result: partial
Using index tag Linclude to rushmore optimize intermediate result
Rushmore optimization level for intermediate result: partial
Using index tag Linclude to rushmore optimize intermediate result
Rushmore optimization level for intermediate result: partial
Using index tag Linclude to rushmore optimize intermediate result
Rushmore optimization level for intermediate result: partial
Using index tag Linclude to rushmore optimize intermediate result
Rushmore optimization level for intermediate result: partial
Using index tag Linclude to rushmore optimize intermediate result
Rushmore optimization level for intermediate result: partial
Using index tag Linclude to rushmore optimize intermediate result
Rushmore optimization level for intermediate result: partial
Using index tag Linclude to rushmore optimize intermediate result
Rushmore optimization level for intermediate result: partial
Joining intermediate result and intermediate result using index tag _36k0kwh9r
Joining intermediate result and intermediate result using index tag _36k0kwha9
Joining intermediate result and intermediate result using index tag _36k0kwhac
Joining intermediate result and intermediate result using index tag _36k0kwh9n
Joining intermediate result and intermediate result using index tag _36k0kwh9u
Joining intermediate result and intermediate result using index tag _36k0kwh9y
Joining intermediate result and intermediate result using index tag _36k0kwha2
Joining intermediate result and intermediate result using index tag _36k0kwh8u
Joining intermediate result and intermediate result using index tag _36k0kwha5
Joining intermediate result and intermediate result using index tag _36k0kwh9k
Joining intermediate result and intermediate result using index tag _36k0kwh9e
Joining intermediate result and intermediate result using index tag _36k0kwhaf