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:

SELECT soh.SalesOrderNumber,
       sod.OrderQty,
       sod.UnitPrice,
       p.Name
FROM Sales.SalesOrderHeader AS soh
    JOIN Sales.SalesOrderDetail AS sod
        ON sod.SalesOrderID = soh.SalesOrderID
    JOIN Production.Product AS p
        ON p.ProductID = sod.ProductID;

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

CREATE OR ALTER VIEW dbo.SalesInfo
AS
SELECT soh.SalesOrderNumber,
       sod.OrderQty,
       sod.UnitPrice,
       p.Name
FROM Sales.SalesOrderHeader AS soh
    JOIN Sales.SalesOrderDetail AS sod
        ON sod.SalesOrderID = soh.SalesOrderID
    JOIN Production.Product AS p
        ON p.ProductID = sod.ProductID;

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:

WHERE soh.SalesOrderNumber = 'SO56271';

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.

The post A View Will Not Make Your Query Faster appeared first on Grant Fritchey.