Hi David
Full optimization is a bit misleading. If you have a million records with a deleted index, a million bytes will be sent across the wire. That is OK on a local hard drisk, but can be bad on a LAN. Sergey said you should test your query with SET DELETED OFF. If you get full optimization without the DELETED() index then you're fine.
>Mike,
>These are vfp queries. As I reported in my other reply, the exists performed a tiny bit slower.
>I didn't know that the in query handled nulls differently (returns no records if there are any nulls) until very recently... Naomi clued me in to this. Knowing this, I preprocess the data set so I know there are no nulls. I clicked the link you reference, but it wasn't found.
>
>Regarding arete-erp, yes, on my list of things to do! This is a dotnetnuke website.. my trial copy of sql server2008r2 that supports it on the back end, expired, so the site is now dead.
>
>Regarding dropping other joins, yes, I'm intending to add that to the logic.
>
>
>>
>In SQL Server there is virtually no performance difference between EXISTS and IN as you are applying them. EXISTS handles NULLS more like regular people might expect and it can handle multiple column comparisons. Gail Shaw is one of the SQL greats. She blogged about your question
http://sqlinthewild.co.za/index.php/2009/08/17/exists-vs-in/ and showed it scaled up, but didn't touch on the NULL aspect.
>>
>>Drop every comparison or join you can depending on the user's choices.
>>
>>By the way,
http://www.arete-erp.com/ is giving an error.
>>
>>>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
