By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,766 Members | 1,252 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,766 IT Pros & Developers. It's quick & easy.

Sql Server Performance

P: n/a
Hello Everyone,
Regarding stored procedures and views, I know that stored procedures
cause SQL Server to create a cached execution plan. Is the same thing
done for views? Also, how bad is the performance hit for a stored
procedure that use 1 or a few views as opposed to re-creating the same
select statement with the proper joins to the required tables?

I know that there are a bunch of variables that affect this stuff, I
just trying to get a ball park idea of how this stuff works.

Thanks,
Frank

Mar 16 '06 #1
Share this Question
Share on Google+
4 Replies


P: n/a
Stu
There is no performance benefit to using stored procedures as opposed
to views; as of SQL Server 7, the optimizer caches all SQL plans the
same way. However, if your queries are highly paramaterizable (as most
stored procedures are), then the likelihood increases that a plan will
get re-used. The optimizer should also process a view in the smae
method as your SELECT statement, so there should be no performance
difference there.

Of course, if you use nested views, you may get some benefit by using
parameterized SELECT statements to filter your data at the lowest level
of the nesting structure, rather than using the views themselves.

HTH,
Stu

Mar 16 '06 #2

P: n/a
Thank you
-Frank

Mar 16 '06 #3

P: n/a
Frank Villasenor (ja*****@gmail.com) writes:
Regarding stored procedures and views, I know that stored procedures
cause SQL Server to create a cached execution plan. Is the same thing
done for views? Also, how bad is the performance hit for a stored
procedure that use 1 or a few views as opposed to re-creating the same
select statement with the proper joins to the required tables?


Views are essentially macros that are expanded, and the plan is built for
the expanded query.

As Stu says, SQL server caches plans for stored procedures as well as
ad hoc-statements.

The performance hit there could be for using a view, rather than a direct
SELECT is that the view refers to tables that the query at had does
not need.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Mar 16 '06 #4

P: n/a
stored procedures can be significantly faster then views.

teh execution plan is relatviely trivial. having an EFFICIENT execution
plan given the known variables can alllow the stored procedure to be
much faster then the view, where the known variables are not known
until execution.

IMO, views suck.

Mar 17 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.