SQL Server: Will the engine execute things from a view that are not needed?

Question

If i have a view that generates multiple columns:

CREATE VIEW dbo.foo AS
SELECT 
    id,
    SUM(SELECT [...] ) AS c1,
    STDEV(SELECT [...] ) AS c2,
    AVG(SELECT [...] ) AS c3,
    MIN(SELECT [...] ) AS c4,
    MAX(SELECT [...] ) AS c5,
    SUM(SELECT [...] ) AS c6,
    [...]
    COUNT(SELECT [...] ) AS cN,
FROM Table
GROUP BY id

But i end up only asking for one of those calculated values:

SELECT id, c382
FROM foo
WHERE id = 42

Will SQL Server calculate all column values, only to ignore them?

In my experience, the answer seems to be "yes". A query:

SELECT id, c382
FROM foo
WHERE id = 42

will be slower than i think it should be. If i break the abstraction, duplicating the code i want:

SELECT id, c382
FROM (
      SELECT 
       id,
       MAX(SELECT [...] ) AS c382
   FROM Table
   GROUP BY id
) AS MiniView
WHERE id = 42

The query runs better.

Or perhaps it's in more real world constructs that it begins to fall apart:

SELECT bar.*, foo.384
FROM bar
    INNER JOIN foo
    ON bar.Bing = foo.id
WHERE bar.Reticulated = 'splines'

Am i crazy, or is SQL Server (2000) crazy?

Answer 1

I don't understand the last part of your question, but the first part is a true statement and sums up the problem with views.

While views can help to make a db more understandable, it is rare they make it more performant exactly because of issues like this.

Best way to think of views is as macros for storing select statements. The place the view is referenced will be replaced by the contents of the view exactly.

Most of the time you can do much better with a stored procedure IMO.

Answer 2

Yes, the view will calculate all your aggreagte columns, only to use the one you specified.

Unfortunately that is the way of views.

You could however refrein from creating so many columns in a view that would not be used?

Answer 3

You could make a materialized view (one that is indexed and improve performance, but note that you can't do that on a view that calls a view (and if you think performance is bad now, wait until you try that).

Now for my cautionary tale about using views extensively in a database. We had some application developers who chose to use views, they never directly accessed a table only views. Design time was great, everything seemed wonderful and looked easy to maintain. Put the website live and loaded extensive customer data instead of small dataset they tested with, timeouts started on day one. As time went on, they changed more and more things as client requirements changed. Each time they did it through a view. Eventually views called views which called views, performance (already bad) plumeted. Finally they create a view that had so many views in it that it ran into the limit on the number of tables that can be referenced in a query (now mind these were all the same tables just all being referenced mulitple times in the views which called each other). Client got angry with poor performance that nobody seemed able to fix (After all it would involve a redesign from the ground up of every query in the application) and took the business elsewhere. This was the largest client that company had - not so good for developers who did the design.

Answer 4

This is how it typically works in SQL Server 2000. This is also largely fixed in SQL Server 2005 and later.