A View Will Not Make Your Query Faster

Home / SQL Server 2017 / A View Will Not Make Your Query Faster

Twice recently, one on a blog post, and one in a forum post, I’ve seen people state, unequivocally, without reservation or hint of a caveat, that, “Oh, just put that query into a view. It will run faster.”

To quote the kids these days…

Time for a rant.

But First…

Frequently when I post something that says, “Query Y runs faster than Query Red”, I get responses from people saying, “Yeah, but if you run Query Red more than once…” or “Query Red was experiencing blocking…” or “You can’t say Query Y is ALWAYS faster…”

So, before we go down that road, a quick note on methodology. First, I’ll be using Adventureworks because, reasons. Second, I won’t run any of the following queries once. When doing something like this, I’ll run them 20-50 times each to eliminate disparity caused by compile times (unless that’s what I’m trying to measure and then I’ll toss in a recompile hint to ensure that happens), caching the data (unless I’m trying to measure that) and general blocking on the system (unless… you get it). I will use extended events to measure performance because I find it causes the least amount of observer effect. I’ll aggregate the runs and what I post will be averages across the runs. GO 50 for the WIN!

On With Our Show

One day, it’s going to happen. I’m going to hear some crazy theory about how SQL Server works and I’m going to literally explode. Instead of some long silly rant with oddball literary & pop culture references you’ll get a screed the size of Ulysses (and about as much fun to read). However, for the moment, like Robin Williams describing a dance move, I’m going to keep it all inside. Here’s our query:

No, no where clause because we have to compare this to this, our view:

I’m not going to insult your intelligence (unless you want to argue that the view is going to be faster) and show you different execution times and all the other results. The query ran in 957ms and the view ran in 932ms. That’s actually a 2.5% difference in favor of the view. Yeah, after 20 runs. Yes, both. Let’s try it again. Second run, the query ran in 926ms and the view in 951ms. That’s about an identical switch in the other direction. In short, they’re running equally quickly.

Can we be done? No? Not yet. What about a WHERE clause you say? Fine.

Added a WHERE clause… just for you:

First run of fifty, the query ran in 160 microseconds. The view ran in 248 microseconds.

WHAT!!!

Let’s stop there right. We proved it. Views suck. They run slower. Rationality wins…

Of course not. We’re talking microseconds. Let’s do another run to see if the variation holds. 144 to 299.

I won’t lie, I’m actually surprised. They should be nearly identical. Let’s check the execution plans:

You can click to make it larger, but except for one glaring point, these plans are identical. In every single regard (but one big one). The big point? The view attempted to go through simple parameterization. If you check the Statementparameterizationtype value, you’ll note that it’s 0. That sounds familiar doesn’t it?

Now don’t get excited. The number of reads on these two queries through all the tests, regardless of duration, has been identical. I’m positive we’re seeing some sort of compiler artifact for why it tried to parameterize this query from the view, but not from the ad hoc query. I’m setting out to prove that these queries are going to perform identically, not that the view will suck (although, blows GIANT holes in the initial premise doesn’t it).

Oh, to heck with it, I’m taking the win.

But… This One Time

I know, I know, at band camp.

There could be any number of explanations for why, this one time, you had a query against a view that ran faster than just a query. Did simplification occur? A successful parameterization attempt? Who the heck knows without details. However, I can tell you, from the bottom of my evil, and UPSET, heart, that a view is a query. A query is a query. The optimizer, largely, treats views and queries the same way.

If we dig, can we find an exception to this statement? Sure. We can. However, the question for me is not can we find an exception to the rule, it’s whether or not this is a rule at all. I think if you do honest testing, you’re going to find that a view and a query, in the vast majority of cases, will perform absolutely the same, not even one better than the other.

You are NOT going to get consistent, different, BETTER, behavior because you turned your query into a view. You’re not. Won’t happen. Doesn’t work like that. Please, stop saying things like that in the forums and on your blog. Just stop.

In fact, depending on what you’re doing with a view (joining to another view which calls other views joined to views, etc.), you may in fact see radically WORSE performance than if you just wrote the query.

Write the query.


Want more? No, not more GIFs, but more on how to use tools to understand how query performance is behaving? I’ve got an all day seminar I’m doing at the following locations and dates:

For SQLSaturday NYC on May 18, 2018. Go here to register.

For SQLSaturday Indianapolis (and my first time speaking in Indiana) on August 10, 2018. Please go here to sign up.

For SQLSaturday Oslo on August 31, 2018. Click here right now to register.

10 Comments

  • Jason A

    I wonder if this misconception came about from thinking views in MS SQL behave like “materialized views” in Oracle. To quote docs.oracle: “A materialized view is a database object that contains the results of a query” Which would imply that a materialized view would be faster than the equivalent query, simply because the results have already been retrieved and stored (which would also explain the necessity to refresh said views in Oracle occasionally.)

  • Jason

    I have made a point or explaining this to the people I have trained in SQL server, so doing my bit on this one Grant. And now I have an article to send them, thanks.

    Also, now I have a mental image of Grant pointing and saying emphatically: “You do an eclectic celebration of the dance!”

  • Toby Ovod-Everett

    One minor caveat – using the RECOMPILE hint in the query can result in totally different query plans than would result from a query compiled without the hint. The RECOMPILE hint in the query is allowed to generate a query plan that only works for the current values in variables at the time of execution. For instance, you could have “@IsFoo = 1 OR (some incredibly complicated EXISTS query)” – if @IsFoo happens to be 1 at execution time, OPTION (RECOMPILE) will completely eliminate the (some incredibly complicated EXISTS query) from the query plan because it’s unnecessary.

    With that in mind, using OPTION (RECOMPILE) in that scenario can speed up optimization because the optimizer doesn’t have to consider the impact of (some incredibly complicated EXISTS query).

    • Yep. All true. But then, you also sacrifice plan reuse and add CPU overhead. That can be a fair tradeoff, depending, but it might also be a knife to the gut of the server, depending. It’s not something I would choose as a default.

OK, fine, but what do you think?

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