March 27, 2002 at 11:40 am
Hi everybody?
What will be from performance point of view the difference using in a stored procedure a select statement or a view (containing the same select statement)?
Thank you,
Duruguru
March 27, 2002 at 3:33 pm
Performance gains on regular SQL selects in that SQL will store execution plan so that SP can access and know (hopefully) quickest path to finding data without needing to check index statistics. You also have more control in SP's in that you do not (in most all cases) have to grant any access to underlying table to login just SP.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
March 28, 2002 at 5:36 am
Views are very similar - really just a way to encapsulate a select that you'll be re-using a lot. Also tends to get used in those cases where there are some uers who should not have access to a couple columns, far easier to implement a view than it is to deal with column level security.
Andy
March 28, 2002 at 6:44 am
I was just wondering if it makes sense that for all the select statements that I have in the SP to create views can use them in the same SP.
March 28, 2002 at 7:09 am
If you can get good code reuse out of building the view, why not?
Andy
April 1, 2002 at 3:41 pm
If however each SP has variances in the SELECTion field output then a common view can actually hurt performance since (as I have come to understand) the view will read all the columns listed in it's select then pass to the stored procedure, you will also suffer a little less if they do match still because the view reads all the columns in it's deffinition. It is similar to doing
SELECT columnlist FROM (SELECT columnlist FROM tblX) AS ViewTable.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply