Top 3 Tips You Need to Know to Write Faster SQL Views

Friend or foe? SQL Server views have been a subject of heated debates when I was in my first year using SQL Server. They said it was bad because it was slow. But how about today?

Are you on the same boat as I was many years ago? Then, join me on this journey to unravel the real deal about SQL views so that you can write them the fastest possible.

SQL views are virtual tables. The records in a view are the result of a query inside it. Whenever the base tables used in the view get updated, it updates the view too. You can also INSERT, UPDATE, and DELETE records in a view as a table in some cases. Though I haven’t tried this myself.

Similarly to a table, you can CREATE, ALTER, or DROP a view. You can even create an index, with some restrictions.

Note that I used SQL Server 2019 in the sample codes.

1. Know the Proper and Improper Use of SQL Views

First, the basics.

What are SQL views for?

It is crucial. If you use it as a hammer to a screwdriver, forget about faster SQL views. First, let’s recall the proper use:

• To focus, simplify, and customize the perception each user has of the database.
• To allow the users access to the only information they need to see for security reasons.
• To provide backward compatibility to an old table or an old schema to not break dependent apps. It is temporary until all the needed changes are complete.
• To partition data coming from different servers. Therefore, they appear as if they are one table from one server or instance.

How NOT to use SQL Server views?

• Reuse the view in another view that will be reused in yet, another view. In short, deeply nested views. The code reuse has a few drawbacks in this case.
• Save on keystrokes. It relates to the first one, which reduces finger pressure and seems to accelerate coding.

Improper use of views, if permitted, will obscure the real reason why you create views. As you will see later, the real benefits outweigh the perceived benefits of improper usage.

Example

Let’s inspect an example from Microsoft. The vEmployee view from AdventureWorks. Here’s the code:

-- Employee names and basic contact information
CREATE VIEW [HumanResources].[vEmployee] 
AS 
SELECT 
    e.[BusinessEntityID]
    ,p.[Title]
    ,p.[FirstName]
    ,p.[MiddleName]
    ,p.[LastName]
    ,p.[Suffix]
    ,e.[JobTitle]  
    ,pp.[PhoneNumber]
    ,pnt.[Name] AS [PhoneNumberType]
    ,ea.[EmailAddress]
    ,p.[EmailPromotion]
    ,a.[AddressLine1]
    ,a.[AddressLine2]
    ,a.[City]
    ,sp.[Name] AS [StateProvinceName] 
    ,a.[PostalCode]
    ,cr.[Name] AS [CountryRegionName] 
    ,p.[AdditionalContactInfo]
FROM [HumanResources].[Employee] e
    INNER JOIN [Person].[Person] p
 ON p.[BusinessEntityID] = e.[BusinessEntityID]
    INNER JOIN [Person].[BusinessEntityAddress] bea 
        ON bea.[BusinessEntityID] = e.[BusinessEntityID] 
    INNER JOIN [Person].[Address] a 
        ON a.[AddressID] = bea.[AddressID]
    INNER JOIN [Person].[StateProvince] sp 
        ON sp.[StateProvinceID] = a.[StateProvinceID]
    INNER JOIN [Person].[CountryRegion] cr 
        ON cr.[CountryRegionCode] = sp.[CountryRegionCode]
    LEFT OUTER JOIN [Person].[PersonPhone] pp
        ON pp.BusinessEntityID = p.[BusinessEntityID]
    LEFT OUTER JOIN [Person].[PhoneNumberType] pnt
        ON pp.[PhoneNumberTypeID] = pnt.[PhoneNumberTypeID]
    LEFT OUTER JOIN [Person].[EmailAddress] ea
        ON p.[BusinessEntityID] = ea.[BusinessEntityID];
GO

The purpose of this view focuses on employee basic information. If needed by a human resource staff, it can be displayed on a web page. Was it reused in other views?

Try this:

1. In SQL Server Management Studio, look for the AdventureWorks database.
2. Expand the Views folder and look for [HumanResources].[vEmployee].
3. Right-click it and select View Dependencies.

If you see another view depending on this view, which then depends on a different view, Microsoft gave us a bad example. But then, there are no other view dependencies.

Let’s move on to the next.

2. Debunk the Myth on SQL Views

When SQL Server processes a SELECT from a view, it evaluates the code in the view BEFORE it deals with the WHERE clause or any join in the outer query. With more tables joined, it will be slow compared to a SELECT from base tables with the same results.

At least, it is what I was told when I started using SQL. Whether it’s a myth or not, there’s only one way to find out. Let’s turn to a practical example.

How SQL Views Work

Microsoft didn’t leave us in the dark to debate endlessly. We have the tools to see how queries work, such as STATISTICS IO and the Actual Execution Plan. We will use these in all our examples. Let’s have the first one.

USE AdventureWorks
GO

SELECT * FROM HumanResources.vEmployee e
WHERE e.BusinessEntityID = 105

To see what is going on when SQL Server processes the view, let’s inspect the Actual Execution Plan in Figure 1. We compare it with the CREATE VIEW code for vEmployee in the previous section.

As you can see, the first nodes processed by SQL Server are the ones using INNER JOIN. Then, it proceeds to process the LEFT OUTER JOINs.

Since we cannot see a Filter node anywhere for the WHERE clause, it must be in one of those nodes. If you inspect all nodes’ properties, you will see the WHERE clause processed in the Employee table. I have enclosed it in a box in Figure 1. To prove that it’s there, see Figure 2 for the Properties of that node:

Analysis

So, had the SELECT statement in the vEmployee view been evaluated or processed BEFORE the WHERE clause was applied? The Execution Plan shows that it had not. If it was, it should appear nearest the SELECT node.

What I was told was a myth. I was avoiding something good because of a misunderstanding of the proper SQL views usage.

Now that we know how SQL Server processes a SELECT from a view, the question remains: Is it slower than not using a view?

SELECT FROM View vs. SELECT FROM Base Tables – Which One Will Run Faster?

First, we need to extract the SELECT statement inside the vEmployee view and produce the same result we had when using the view. The code below shows the same WHERE clause:

USE AdventureWorks
GO

-- SELECT FROM a view
SELECT * FROM HumanResources.vEmployee e
WHERE e.BusinessEntityID = 105

-- SELECT FROM Base Tables
SELECT 
    e.[BusinessEntityID]
    ,p.[Title]
    ,p.[FirstName]
    ,p.[MiddleName]
    ,p.[LastName]
    ,p.[Suffix]
    ,e.[JobTitle]  
    ,pp.[PhoneNumber]
    ,pnt.[Name] AS [PhoneNumberType]
    ,ea.[EmailAddress]
    ,p.[EmailPromotion]
    ,a.[AddressLine1]
    ,a.[AddressLine2]
    ,a.[City]
    ,sp.[Name] AS [StateProvinceName] 
    ,a.[PostalCode]
    ,cr.[Name] AS [CountryRegionName] 
    ,p.[AdditionalContactInfo]
FROM [HumanResources].[Employee] e
    INNER JOIN [Person].[Person] p
	ON p.[BusinessEntityID] = e.[BusinessEntityID]
    INNER JOIN [Person].[BusinessEntityAddress] bea 
        ON bea.[BusinessEntityID] = e.[BusinessEntityID] 
    INNER JOIN [Person].[Address] a 
        ON a.[AddressID] = bea.[AddressID]
    INNER JOIN [Person].[StateProvince] sp 
        ON sp.[StateProvinceID] = a.[StateProvinceID]
    INNER JOIN [Person].[CountryRegion] cr 
        ON cr.[CountryRegionCode] = sp.[CountryRegionCode]
    LEFT OUTER JOIN [Person].[PersonPhone] pp
        ON pp.BusinessEntityID = p.[BusinessEntityID]
    LEFT OUTER JOIN [Person].[PhoneNumberType] pnt
        ON pp.[PhoneNumberTypeID] = pnt.[PhoneNumberTypeID]
    LEFT OUTER JOIN [Person].[EmailAddress] ea
        ON p.[BusinessEntityID] = ea.[BusinessEntityID]
WHERE e.BusinessEntityID = 105

Then, we inspect the STATISTICS IO and do a Compare Showplan. How many resources will a query from a view need in comparison to querying from base tables? See Figure 3.

Here, querying from a view or base tables will consume the same logical reads. Both used 19 * 8KB pages. Based on this, it’s a tie on who is faster. In other words, using a view won’t hurt performance. Let’s compare the Actual Execution Plan of both using the Compare Showplan:

Do you see the shaded portion of the diagram? How about the QueryPlanHash of both? Since both queries have equal QueryPlanHash and the same operations, either view or base tables will be processed the same by SQL Server.

The same logical reads and the same plan of the sample tell us that both will perform the same. Thus, having high logical reads will make your query run slow whether you use views or not. Knowing this fact will help you fix the problem and make your view run faster.

Unfortunately, there’s some bad news.

Joining SQL Views to Tables

What you saw earlier is a SELECT from a view with no joins in it. However, what if you join a table to a view?

Let’s review another example. This time, we use the vSalesPerson view in AdventureWorks – a salespeople list with contact information and sales quota. Again, we compare the statement to a SELECT from base tables:

-- get the total sales orders for each salesperson
-- using the view joined with SalesOrderHeader
SELECT 
 sp.FirstName
,sp.MiddleName
,sp.LastName
,SUM(soh.TotalDue) AS TotalSalesOrders
FROM Sales.vSalesPerson sp
INNER JOIN Sales.SalesOrderHeader soh ON sp.BusinessEntityID = soh.SalesPersonID
GROUP BY sp.LastName, sp.MiddleName, sp.FirstName

-- using base tables
SELECT
 p.FirstName
,p.MiddleName
,p.LastName
,SUM(soh.TotalDue) AS TotalSalesOrders
FROM sales.SalesPerson sp
INNER JOIN Person.Person p ON sp.BusinessEntityID = P.BusinessEntityID
INNER JOIN Sales.SalesOrderHeader soh ON sp.BusinessEntityID = soh.SalesPersonID
GROUP BY p.LastName, p.MiddleName, p.FirstName 

If you think it will also be the same, check the STATISTICS IO:

Surprised? Joining the vSalesPerson view with the SalesOrderHeader table needs tremendous resources (28,240 x 8KB) compared to just using the base tables (774 x 8KB). Notice also that it included some tables we didn’t need (the tables within red boxes). Let alone higher logical reads on SalesOrderHeader when using the view.

But it doesn’t end there.

The Actual Execution Plan Reveals More

Notice the Actual Execution Plan of the query to base tables:

The illustration seems to show a pretty normal Execution Plan. But check out the one with the view:

The execution plan in Figure 7 coincides with the STATISTICS IO in Figure 5. We can see the tables we don’t need from the view. There is also a Key Lookup node with a row estimate that’s more than a thousand records off than the actual rows. Finally, a warning in the SELECT node also appears. What could it be?

What is that ExcessiveGrant warning in the SELECT node?

An Excessive Grant happens when the maximum used memory is too small compared to the granted memory. In this case, 1024KB was granted, but only 16KB was used.

Memory Grant is the estimated amount of memory in KB required to run the plan.

It could be the wrong estimates in the Key Lookup node and/or the inclusion of the tables we didn’t need in the plan that caused this. Also, too much-granted memory can cause blocking. The remaining 1008KB could have been useful for other operations.

Eventually, something went wrong when you joined the view with a table. We don’t have to deal with these problems if we query from the base tables.

Takeaways

It was a long explanation. However, we know that views are not evaluated or processed BEFORE a WHERE clause or joins in the outer query are evaluated. We also proved that both would perform the same.

On the other hand, there’s a case when we join a view to a table. It uses joins of tables we don’t need from the view. They are invisible to us unless we check the STATISTICS IO and the Actual Execution Plan. It all can hurt performance, and problems may come out of nowhere.

Therefore:

• We should know how queries, including views, work from the inside.
• STATISTICS IO and Actual Execution Plans will reveal how queries and views will work.
• We cannot just join a view to a table and reuse it carelessly. Always check the STATISTICS IO and Actual Execution Plans! Instead of reusing views and nesting them for “improved” coding productivity, I use an IntelliSense and the code completion tool like SQL Complete.

We can then be sure not to write views that will have correct results but run like a snail.

3. Try Indexed Views

Indexed views are what the name implies. It can give a performance boost to SELECT statements. But like table indexes, it can impact performance if the base tables are large and continuously updated.

To see how indexed views can improve query performance, let’s examine the vStateProvinceCountryRegion view in AdventureWorks. The view is indexed on StateProvinceID and CountryRegionCode. It is a clustered, unique index.

Let’s compare the STATISTICS IO of the view not having the index and having an index. With this, we learn how many 8KB pages our SQL Server will read:

The Figure shows that having an index in the vStateProvinceCountryRegion view reduces logical reads by half. It is a 50% improvement compared to not having an index.

That’s good to hear.

Still, again, don’t add indexes to your views carelessly. Besides having a long list of strict rules to have 1 unique, clustered index, it can hurt performance, much like adding indexes to tables jauntily. Also, check the STATISTICS IO if there’s a decrease in logical reads after adding the index.

Takeaways

As we have seen in our example, indexed views can improve the SQL views performance.

BONUS Tip

Just like any other query, SQL views will run fast if:

• Statistics are updated
• Missing indexes are added
• Indexes are defragmented
• Indexes used the right FILLFACTOR

Conclusion

Are SQL views good or bad?

SQL views are good if we write them correctly and check to see how they will be processed. We have tools like STATISTICS IO and Actual Execution Plan – use them! Indexed views can also improve performance.

Like this post? Please share some love on your favorite social media platform.

Tags: execution plan, indexed view, performance, sql complete, sql server views Last modified: September 18, 2021