do views increase performance?

I’m a huge fan of views. I’m always understood that queries that hit a view are rewritten into simpler SQL Statements, but I wanted to demonstrate some examples where hitting a view is ~100 times faster than hitting the base tables.

In this situation, the view ‘vw000022010’ is a union of a bunch of different partitions (one partition per STATE).

This is a small snippet of the view definition (limited to two states for demonstration purposes, but the real view has 50 UNION statements)

SELECT ‘ak’ AS State, F1 AS FILEID, F2 AS STUSAB, F3 AS CHARITER, F4 AS CIFSN, F5 AS LOGRECNO, F6 AS P0020001, F7 AS P0020002, F8 AS P0020003, F9 AS P0020004, F10 AS P0020005,
F11 AS P0020006
FROM ak000022010 WITH (NOLOCK)
UNION ALL
SELECT ‘al’ AS State, F1 AS FILEID, F2 AS STUSAB, F3 AS CHARITER, F4 AS CIFSN, F5 AS LOGRECNO, F6 AS P0020001, F7 AS P0020002, F8 AS P0020003, F9 AS P0020004, F10 AS P0020005,
F11 AS P0020006
FROM al000022010 WITH (NOLOCK)

Notice how the first column is a constant state = ‘ak’.

Now notice how querying that CONSTANT is 99x faster than querying the raw data– primarily because the SQL optimizer is smart enough to say ‘oh, I only need to check this one partition’.

You just can’t do this with stored procedures.  Views are magical magical inventions.. they are BETTER at rewriting SQL, because you can add constants, etc

select G.County, D.*
From vw000022010 D
inner join AAP_Geography.dbo.DimGeography G
on g.LOGRECNO = D.LOGRECNO
and g.STUSAB = D.state
and g.sumlev = ‘050’
and D.state = ‘AK’

select G.County, D.*
From vw000022010 D
inner join AAP_Geography.dbo.DimGeography G
on g.LOGRECNO = D.LOGRECNO
and g.STUSAB = D.STUSAB
and g.sumlev = ‘050’
and D.state = ‘AK’

view99

This is just my first example, I will try to write up my 2nd example soon (querying view ‘passes through’ other queries).  For this other topic, I was amazed to see how much faster a view was than a dump of the views, even after adding some indexes.

Join the Conversation

2 Comments

  1. Thanks for a good example. I like this trick, and does show a specific scenario when a view is helpful.

    I’m not sure the lesson, though, is that “generally speaking, views are faster than queries” as much as it is something like “when querying a partitioned table, always join on the partitioning key” (that’s basically what you have here, a manually partitioned set of tables, very similar to what SQL partitioning does behind the scenes).

    But yes, I can see how dumping all 50 tables together into a result set would be slower than what you have, since in your case SQL ends up querying just the one underlying table. (A better comparison might be querying the underlying ak000022010 table directly, in which case I would predict similar performance.)

    But I’ll still hold to my answer to the original StackOverflow question:

    Is “select * from myView”
    faster than
    “select * from ([query to create same resultSet as myView])”

    Generally speaking, unless the view is indexed, it is not faster.

Leave a comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.