Twice recently, I’ve come across statements claiming that putting a query into a view will make it run faster. However, it’s time to debunk this myth and shed some light on the truth. Let’s dive into the details.

Before we proceed, let’s establish the methodology used for this analysis. To eliminate any disparities caused by compile times, caching, or system blocking, I ran each query 20-50 times and averaged the results. I also used extended events to measure performance, as it minimizes the observer effect.

Now, let’s take a look at a specific query and its corresponding view:

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;

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;

Running these queries multiple times, I found that there was only a 2.5% difference in favor of the view. In subsequent runs, the performance switched back and forth, indicating that both the query and the view were running equally quickly.

But what happens when we introduce a WHERE clause? Let’s find out:

WHERE soh.SalesOrderNumber = 'SO56271';

In the first run, the query executed in 160 microseconds, while the view took 248 microseconds. However, these differences are negligible, considering the execution times. Subsequent runs showed similar variations, indicating that the performance of the query and the view remained comparable.

Examining the execution plans, we found that they were nearly identical, except for one significant difference. The view attempted to go through simple parameterization, which could explain the slight variation in performance. However, the number of reads for both queries remained consistent throughout the tests.

Based on these findings, it is clear that a view is essentially a query. The optimizer treats views and queries in a similar manner, resulting in comparable performance. While there may be exceptions to this rule, they are rare and do not negate the overall conclusion.

Therefore, it is important to dispel the misconception that turning a query into a view will automatically improve its performance. In fact, depending on the complexity of the view and its underlying joins, using a view may even lead to worse performance compared to writing the query directly.

So, the next time you come across claims that a view will make your query faster, remember that it’s not a guaranteed solution. Instead, focus on writing efficient queries and utilize tools to analyze and optimize query performance.

For more in-depth knowledge on understanding and improving query performance, consider attending my upcoming all-day seminars:

• SQLSaturday NYC on May 18, 2018. Register here.
• SQLSaturday Indianapolis on August 10, 2018. Sign up here.
• SQLSaturday Oslo on August 31, 2018. Click here to register.

Remember, it’s essential to rely on accurate information and avoid spreading misconceptions in the SQL Server community. Let’s focus on understanding the nuances of SQL Server performance and making informed decisions.

Thank you for reading!