March 21, 2008 at 4:58 pm
A process iterates through a list of keys in a table. For each key it must select records to display in an app. from a view named according to the key value. The views are named like: vw_Key_123, vw_Key_456, etc.
To call the view I must SET @SQL = 'select * from vw_Key_' + cast(@Key as varchar), then EXECUTE sp_executesql @SQL.
My question is two-fold:
a. Does storing the view and using it in this way have any perfomance benefits over executing a string that includes the query that is in the view (i.e. @SQL = 'select fields from tables where criteria')? Either way I'm using dynamic SQL, right?
b. Does selecting from a view have performance advantages over running the query that is in the view, assuming the query/view contains 5 or more tables with 5 or more WHERE criteria?
March 21, 2008 at 10:11 pm
Kevin Durham (3/21/2008)
a. Does storing the view and using it in this way have any perfomance benefits over executing a string that includes the query that is in the view (i.e. @SQL = 'select fields from tables where criteria')? Either way I'm using dynamic SQL, right?b. Does selecting from a view have performance advantages over running the query that is in the view, assuming the query/view contains 5 or more tables with 5 or more WHERE criteria?
The advantages of non-materialized/non-indexed views are strictly in terms of reuse, isolation and modularity. And yes, you are still using dynamic SQL.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 22, 2008 at 8:47 pm
I would say no and no
March 24, 2008 at 7:55 am
Thanks for the input. I suspected this was the case.
March 25, 2008 at 6:58 am
Also, from the sounds of things, you're doing RBAR (row-by-agonizing-row) processing. Can't you pass the ID's to your query as a set and then do a join against your data so that you're only calling the query once instead of once per id? You can either use a UDF to parse a delimited string into a table and then join on that or pass in the list of values as XML and join from there. Either way, you should see a pretty major performance increase, even if you're using ad hoc SQL.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply