How do Views Affect SQL Server Performance?

Views in SQL Server are really just queries that have been given a name so that they can be referenced as if they are tables. This can be convenient, especially for code reuse. However, it can also have some unanticipated consequences. Let’s look at an example. Let’s consider the affect joining two views can have on the performance of a SQL Server query.

Evaluating view performance in SQL Server

For this demonstration, we’ll use the BaseballData database mentioned in Data! Getcha Data Here! This data set contains baseball statistics for players and managers going back to 1871. We’ll use the players, pitching, and halloffame tables in our example.

Creating SQL views for our example

Let’s start by creating a couple of views: one that returns all players who were named to the Baseball Hall of Fame, vw_hof, and another that returns all pitchers, vw_pitchers. The view definitions are below. Notice that each of these views references the dbo.players table. That will become important later.

CREATE VIEW vw_hof
AS
    --hall of famers
    SELECT p.playerID,
        p.nameFirst,
        p.nameLast,
        p.nameNick,
        h.yearID
    FROM dbo.players AS p
        JOIN dbo.halloffame AS h ON h.hofID = p.hofID;

GO

CREATE VIEW vw_pitchers
AS
    --pitchers 
    SELECT p.playerID,
        p.nameFirst,
        p.nameLast,
        p.nameNick,
        f.yearID,
        f.teamID
    FROM dbo.pitching AS f
        JOIN dbo.players AS p ON p.playerID = f.playerID;

Looking at SQL view performance

Now that we’ve created the views, let’s examine the performance when we use them together in a query. With query statistics on and actual execution plan enabled, let’s run test query that returns all pitchers who were inducted into the Baseball Hall of Fame. We can use the following query.

--hall of fame pitchers
SELECT p.playerID,
    p.nameFirst,
    p.nameLast,
    p.nameNick,
    h.yearID
FROM vw_hof AS h
    JOIN vw_pitchers AS p ON p.playerID = h.playerID;

Running the query, let’s look at the actual query plan used by SQL Server to resolve the query. Reading from right to left, the vw_hof view joins the dbo.players table to the dbo.halloffame table. Then, the dbo.pitching table from the vw_pitchers view is joined to the output. And finally, the dbo.players table is scanned again. That’s additional work that doesn’t add value.

Let’s look at the query statistics. Again, we can see the dbo.players table was scanned twice, and the logical reads for the table was 908.

NOTE: You can see from the query plan that this database would benefit from some index analysis. Certainly, proper indexing would cause a different query plan to be created and the resulting performance would be less impactful than the Clustered Index Scan. Please don’t take this simple example as anything other than that.

Rewriting to improve performance without using views

For comparison, let’s rewrite the query to eliminate the views and rerun the query.

--hall of fame pitchers
SELECT p.playerID,
    p.nameFirst,
    p.nameLast,
    p.nameNick,
    h.yearID
FROM dbo.players AS p
    JOIN dbo.halloffame AS h ON h.hofID = p.hofID
    JOIN dbo.pitching AS f ON p.playerID = f.playerID;

The query plan is more streamlined. By removing the views, the Clustered Index Scan on the dbo.players table only occurs once.

We can see this reflected in the query io statistics as well. The dbo.players table was scanned once.

Views and how they affect and SQL Server performance

So, views can have a negative impact on query performance in SQL Server. Joining two views that have common base tables can cause extra work for SQL Server. Nested views, a view that calls another view, can similarly have an adverse affect. We’ve seen both of these conditions in production environments.

But this shouldn’t be considered a blanket statement. Views are not inherently bad. There are many instances where views have no affect at all to a query’s performance.

Want to work with The Sero Group?

Want to learn more about how SERO Group helps organizations take the guesswork out of managing their SQL Servers? It’s easy and there is no obligation. 

Schedule a call with us to get started.