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
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply