Sql Server Performance

[Frank Villasenor]

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

[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

[Frank Villasenor]

Thank you
-Frank

[Erland Sommarskog]

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

[Doug]

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.