Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
Full rushmore in the query ?
Message
From
28/03/2011 13:42:33
 
 
To
28/03/2011 11:15:32
General information
Forum:
Visual FoxPro
Category:
Databases,Tables, Views, Indexing and SQL syntax
Miscellaneous
Thread ID:
01505195
Message ID:
01505238
Views:
35
Craig,
As per my reply to Sergey, which I copied you on, the results when I re-did this after including tags on deleted() show full optimization, as Sergey had stated it would. However, as I also stated to him, I am not understanding why the sys(3054) report doesn't reference the index tags used in the outer query, except for the one on deleted(). Does this have to do with your point?

>You'll never get full Rushmore with more than two tables in the JOIN. That's becase VFP has to create multiple temp tables to resolve the query.
>
>>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
Previous
Reply
Map
View

Click here to load this message in the networking platform