Views, UDFs

[gherrell]

I know there is a lot of information already out there on this topic,
but given the following scenario...

--------------------------------------------------------------------------------------------------------------------------------------
Create a view like so ( pardon the pseudo-code )...

CREATE View vwContactAddresses
Select * FROM Contact INNER JOIN Address ON Contact.ContactID =
Address.ContactID

And then do a sargable select from the view using a stored procedure

CREATE STORED PROCEDURE spSelect_ContactAddresses
@ContactID int
AS
Select * FROM vwContactAddresses WHERE ContactID = @ContactID
--------------------------------------------------------------------------------------------------------------------------------------

In my understanding, "vwContactAddresses" would be substituted with the
actual SQL join statement when the view is accessed.

So for the stored procedure in question an execution plan for
"Select * FROM Contact INNER JOIN Address ON Contact.ContactID =
Address.ContactID WHERE ContactID = @ContactID" would be cached.
Correct?

With regards to execution plan caching, is this not the same as
creating an inline UDF that takes parameters or just creating a stored
procedure that would do the join w/out the view reference?

[Erland Sommarskog]

gherrell (gr**********@gmail.com) writes:

I know there is a lot of information already out there on this topic,
but given the following scenario...

--------------------------------------------------------------------------

------------------------------------------------------------

Create a view like so ( pardon the pseudo-code )...

CREATE View vwContactAddresses
Select * FROM Contact INNER JOIN Address ON Contact.ContactID =
Address.ContactID

And then do a sargable select from the view using a stored procedure

CREATE STORED PROCEDURE spSelect_ContactAddresses
@ContactID int
AS
Select * FROM vwContactAddresses WHERE ContactID = @ContactID
--------------------------------------------------------------------------

------------------------------------------------------------

>
In my understanding, "vwContactAddresses" would be substituted with the
actual SQL join statement when the view is accessed.

So for the stored procedure in question an execution plan for
"Select * FROM Contact INNER JOIN Address ON Contact.ContactID =
Address.ContactID WHERE ContactID = @ContactID" would be cached.
Correct?

Not really. What is in the cache is a plan for the stored procedure
spSelect_ContactAdresses.

While the operators in the plan are likely to be same as in the plans
for "Select * FROM vwContactAddresses WHERE ContactID = @ContactID" and
"Select * FROM Contact INNER JOIN Address ON Contact.ContactID =
Address.ContactID WHERE ContactID = @ContactID" they are three different
cache entries. In fact, the plan is case- and space-sensitive, so all these
three are different cache entries:

SELECT col1 FROM tbl
Select col1 FROM tbl
SELECT col1 FROM tbl


--
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

[gherrell]

That makes sense. However, I didn't express myself very well.
I am looking for validation that this approach with a view gives the
same opportunity for a performance benefit as a parameterized UDF or
straight stored procedure.

Erland Sommarskog wrote:

gherrell (gr**********@gmail.com) writes:

I know there is a lot of information already out there on this topic,
but given the following scenario...

--------------------------------------------------------------------------

------------------------------------------------------------

Create a view like so ( pardon the pseudo-code )...

CREATE View vwContactAddresses
Select * FROM Contact INNER JOIN Address ON Contact.ContactID =
Address.ContactID

And then do a sargable select from the view using a stored procedure

CREATE STORED PROCEDURE spSelect_ContactAddresses
@ContactID int
AS
Select * FROM vwContactAddresses WHERE ContactID = @ContactID
--------------------------------------------------------------------------

------------------------------------------------------------

In my understanding, "vwContactAddresses" would be substituted with the
actual SQL join statement when the view is accessed.

So for the stored procedure in question an execution plan for
"Select * FROM Contact INNER JOIN Address ON Contact.ContactID =
Address.ContactID WHERE ContactID = @ContactID" would be cached.
Correct?

Not really. What is in the cache is a plan for the stored procedure
spSelect_ContactAdresses.

While the operators in the plan are likely to be same as in the plans
for "Select * FROM vwContactAddresses WHERE ContactID = @ContactID" and
"Select * FROM Contact INNER JOIN Address ON Contact.ContactID =
Address.ContactID WHERE ContactID = @ContactID" they are three different
cache entries. In fact, the plan is case- and space-sensitive, so all these
three are different cache entries:

SELECT col1 FROM tbl
Select col1 FROM tbl
SELECT col1 FROM tbl


--
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

[Erland Sommarskog]

gherrell (gr**********@gmail.com) writes:

That makes sense. However, I didn't express myself very well.
I am looking for validation that this approach with a view gives the
same opportunity for a performance benefit as a parameterized UDF or
straight stored procedure.

I'm not really sure what you mean, but using a view should not matter
in theory. That is, SELECT from the view or the SELECT from the base
query in the same thing.

But there is a risk: say that a programmer finds the view and thinks
"hey I get the value from X from this view, and value of Y from that
view". When you expand the query, you see that several tables appears
twice, although it had been sufficient with one. But will the optimizer
see that?

In the system I work with, we have very few views. There is one corner
of the database that I am not inolved with where they choose to use it,
and I believe makes sense there. Myself, I've only used views a few
times when I have rearranged tables, and kept the old definition as view
for compatibility.

--
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