Views and dynamic SQL

Question

Views and dynamic SQL

sql_monkey

SSC Eights!

Points: 867
More actions
March 21, 2008 at 4:58 pm

#180340

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?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

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]

Steve Jones - SSC Editor SSC Guru Points: 728164 More actions · Answer 2

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

sql_monkey SSC Eights! Points: 867 More actions · Answer 3

Thanks for the input. I suspected this was the case.

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 4

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