I modified this so there are indexes on deleted() for each of the subquery cursors, and for the "curdataunfiltered" cursor (also ran with set deleted off). Now the result is full optimization reported across the board. However, what I don't understand is why the sys(3054) only shows results for all of the subqueries, and for the xdel index tag of curdataunfiltered, but not for all of the other indexes of curdataunfiltered - these indexes have the same name as the column names, e.g. accountnam, zip, carrier, etc. (Note: I said there were 13 filters, in fact there are 12) The index tag names report in sys(3054) seem to correspond with the tag name in my subqueries)
It seems like the subqueries are getting fulling optimized, but I am not understanding whether the outer query is.
Also, I modified the query to do an exists query, instead of the in query. The sys(3054) results were the same in either case. The exists was executing in about .067 seconds, the in, in about .048. The sys(3054) results are below:
Using index tag Xdel to rushmore optimize intermediate result
Rushmore optimization level for intermediate result: full
Using index tag Linclude to rushmore optimize intermediate result
Using index tag Xdel to rushmore optimize intermediate result
Rushmore optimization level for intermediate result: full
Using index tag Linclude to rushmore optimize intermediate result
Using index tag Xdel to rushmore optimize intermediate result
Rushmore optimization level for intermediate result: full
Using index tag Linclude to rushmore optimize intermediate result
Using index tag Xdel to rushmore optimize intermediate result
Rushmore optimization level for intermediate result: full
Using index tag Linclude to rushmore optimize intermediate result
Using index tag Xdel to rushmore optimize intermediate result
Rushmore optimization level for intermediate result: full
Using index tag Linclude to rushmore optimize intermediate result
Using index tag Xdel to rushmore optimize intermediate result
Rushmore optimization level for intermediate result: full
Using index tag Linclude to rushmore optimize intermediate result
Using index tag Xdel to rushmore optimize intermediate result
Rushmore optimization level for intermediate result: full
Using index tag Linclude to rushmore optimize intermediate result
Using index tag Xdel to rushmore optimize intermediate result
Rushmore optimization level for intermediate result: full
Using index tag Linclude to rushmore optimize intermediate result
Using index tag Xdel to rushmore optimize intermediate result
Rushmore optimization level for intermediate result: full
Using index tag Linclude to rushmore optimize intermediate result
Using index tag Xdel to rushmore optimize intermediate result
Rushmore optimization level for intermediate result: full
Using index tag Linclude to rushmore optimize intermediate result
Using index tag Xdel to rushmore optimize intermediate result
Rushmore optimization level for intermediate result: full
Using index tag Linclude to rushmore optimize intermediate result
Using index tag Xdel to rushmore optimize intermediate result
Rushmore optimization level for intermediate result: full
Using index tag Linclude to rushmore optimize intermediate result
Using index tag Xdel to rushmore optimize intermediate result
Rushmore optimization level for intermediate result: full
Joining intermediate result and intermediate result using index tag _36k0sf6wd
Joining intermediate result and intermediate result using index tag _36k0sf6wt
Joining intermediate result and intermediate result using index tag _36k0sf6wu
Joining intermediate result and intermediate result using index tag _36k0sf6wc
Joining intermediate result and intermediate result using index tag _36k0sf6we
Joining intermediate result and intermediate result using index tag _36k0sf6wq
Joining intermediate result and intermediate result using index tag _36k0sf6wr
Joining intermediate result and intermediate result using index tag _36k0sf6vu
Joining intermediate result and intermediate result using index tag _36k0sf6ws
Joining intermediate result and intermediate result using index tag _36k0sf6wb
Joining intermediate result and intermediate result using index tag _36k0sf6wa
Joining intermediate result and intermediate result using index tag _36k0sf6x5
>You'll get partial optimization when there's no index on DELETED(). Run your query with SET DELETED OFF to see if it's the case.
>
>>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
