Unpacking the View

April 24, 2009 at 9:16 am (SQL Server 2005, SQL Server 2008, SQLServerPedia Syndication, TSQL) (, , , , )


A view is simply a query that behaves something like a table. Most people know this. Most people also know that a view is simply a mask on top of what might be a very complex query. It all seems really simple. You call the view inside a simple query, the view runs the underlying complex query. Most people might not know that when a view is called and it gets sent to the optimizer, the optimizer unpacks the view and binds the component parts of the query necessary to create an execution plan that will return the data requested. What I didn’t know until recently was that the optimizer is VERY smart. Not only does it unpack the query of the view, but it will change the query that the view uses. Let’s take an example (using AdventureWorks2008):


CREATE VIEW dbo.vTest
AS
 SELECT soh.AccountNumber
,sod.LineTotal
,(SELECT COUNT(pcc.BusinessEntityId)
FROM Sales.PersonCreditCard AS pcc
WHERE pcc.CreditCardID = soh.CreditCardID
) AS PersonCreditCard
FROM Sales.SalesOrderHeader AS soh
JOIN Sales.SalesOrderDetail AS sod
ON soh.SalesOrderID = sod.SalesOrderID ;
GO

Whether or not this query is a good one (I’d say it isn’t) and whatever you might think about it, the view definition here is pretty straight forward. If you run a SELECT against this view and take a look at the execution plan, it looks like this:

vtestexecutionplan4

As you can see, the behavior is completely as defined above. The view has been resolved out to it’s component parts in order to build the query and arrive at the data requested. What happens if, instead of selecting all the columns, only a couple are selected? Well, the query that defines the view is not changing, so the optimizer will arrive at the same execution plan as that shown in Figure 1, right? Run this query and get the execution plan:

SELECT
 vt.AccountNumber
,vt.LineTotal
FROM
 dbo.vTest AS vt;

2columexecutionplan

What the heck just happened?

I told you, the optimizer is smart. Compare that execution plan with the one generated from this query:

 SELECT soh.AccountNumber
,sod.LineTotal
FROM Sales.SalesOrderHeader AS soh
JOIN Sales.SalesOrderDetail AS sod
ON soh.SalesOrderID = sod.SalesOrderID ;

You’ll find that they’re identical. As stated before, views are just a query stored on the side. The optimizer is smart enough to figure out that it only needs to generate a plan as if the query requested was this. But what happens when you start nesting views? Yes, yes, Yeah… I… Ok… But… YES! That’s not a good idea. So? When has that slowed people down for a New York second? I’ve seen views nested three and four layers deep. I’ve seen table valued user defined functions nested six and seven layers deep. The question is, how well does the optimizer handle something like this? This script sets up a new situation:

CREATE VIEW dbo.vSalesInfo
AS
SELECT soh.AccountNumber
,sod.LineTotal
,soh.CreditCardID
,sod.ProductID
FROM Sales.SalesOrderHeader AS soh
JOIN Sales.SalesOrderDetail AS  sod
ON soh.SalesOrderID = sod.SalesOrderID ;

CREATE VIEW dbo.vTest3
AS
SELECT vsi.AccountNumber
,vsi.LineTotal,
(
SELECT COUNT(pcc.BusinessEntityId)
FROM Sales.PersonCreditCard AS pcc
WHERE pcc.CreditCardID = vsi.CreditCardID) AS PersonCreditCard
,(SELECT COUNT(v.BusinessEntityId)
FROM Purchasing.Vendor v) AS VendorCount
,(SELECT COUNT(a.AddressId)
FROM Person.Address AS a) AS AddressCount
,(SELECT COUNT(be.BusinessEntityID)
FROM Person.BusinessEntity be) AS BusinessEntityCount
FROM dbo.vSalesInfo AS vsi

From the script you can see that I’ve nested one view inside another and complicated the outer a view a bit. What happens when you run the query that would eliminate tables from the view this time?

 SELECT vt.AccountNumber
,vt.LineTotal
FROM dbo.vTest3 AS vt

 3rdtest

See. The optimizer is very smart. Ah, but before you get all comfy and crack a bottle of some frothy beverage, we need to look at the compile times. Compare the STATISTICS TIME output from a query that pulls from the full view, the limited pull from the view and the pull from the tables:

–View 1
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 6 ms.

–Table
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 6 ms.

–View 2
SQL Server parse and compile time:
CPU time = 8 ms, elapsed time = 8 ms.

These are small views and easy queries, so the compile times are not large. But with one consistently 30% larger than the others in terms of execution time and actually requiring CPU cycles when the others require less, you begin to see that, despite the extreme intelligence of the optimizer, there is a cost. TANSTAAFL still applies. Keep all this in mind if you think that nested views are a nifty way to write your code or you’re trying to convince someone that nested views are not a nifty way to write their code.

12 Comments

  1. Ben said,

    I have a query that is run against the same db’s but by two different users. One user (me) is the database owner. The other user has select permissions on the required tables. When I run the execution plans, for the same query hitting the same machines I get totally different results. What’s worse, the dbo connection takes about a minute to complete while the ‘user’ connection takes almost 30 minutes to complete. Ideas?

    Thanks

  2. scarydba said,

    The first thing that comes to mind is differences in the connections? Specifically do you have different ANSI settings than the other user? That could lead to the issue you describe. I also have to ask, are you running identical queries with identical parameters? Even small differences could result in different execution plans. Do you have performance governor running limiting the resources available to the other connection?

    Based on the information provided, that’s about the best I can suggest. If none of that seems applicable you might try putting together some test data, the query, & some DDL scripts and posting the same question over on SQL Server Central.

  3. Ben said,

    Yep. Exact same queries. The only difference is the user.

  4. scarydba said,

    What about the other stuff?

  5. Ben said,

    I am running idential queries with identical parameters. I have cut and pasted my query into the user’s environment.

    As far as a performance governor is concerned, we do not use one. Further, doesn’t a governor just limit execution time for certain classes of users?

    Through profiler, I determined that all of the ‘set’ statements are identical. As a result, I am certain that the ANSI settings are identical.

    Thanks again.

  6. scarydba said,

    If you force a recompile on the proc, does the user’s query improve?

  7. Ben said,

    I’m issuing the command through management studio…it’s not a stored proc.

  8. scarydba said,

    Hmmm. It could be parameter sniffing. Are the parameters hard coded within the query or are they passed to a variable and then to the query? If the former, try changing it to the latter.

  9. Ben said,

    The parameters are hard coded in the query. I discovered the problem during a training session. One of my users was demoing an app I built and it took forever to get through this query, which was strange because it ran in a matter of minutes for me. In debugging the issue, I’ve identified the query and (variables and everything) and am just doing my testing with that (the query). Trying to take as many layers away as possible.

    Right now I believe I have an apples to apples comparison.

    Obviously the quick fix to this is to grant dbo permissions to this group of users, but there has to be another way.

    Thanks again for all of your help

  10. PASS Log Reader Award Winners | Brent Ozar - SQL Server DBA said,

    […] Grant Fritchey for Unpacking the View (Blog – RSS – […]

  11. SQL Server Central said,

    […] Grant Fritchey for Unpacking the View (Blog – RSS – […]

  12. PASS Log Reader Award Winners | The SQL UPDATE Statement said,

    […] Grant Fritchey for Unpacking the View (Blog – RSS – […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: