Using Indexed Views to Replace Triggers

Let’s get this out of the way: I think triggers are cool. I also think triggers have their place, they just need to be used with care. One reason for this is that triggers are synchronous. That is – trigger actions will block a command from returning to the client until the the trigger’s actions have completed.

What’s the solution? Do we run summary jobs on a regular basis? Maybe.

Sometimes summary jobs aren’t enough. Sometimes we have to update summarized data in real time. Let’s break out AdventureWorks and look at an example.

Let’s say we have a report that shows us the total line item sales per customer broken out by year and month.

That’s pretty simple, right?

SELECT  p.BusinessEntityID ,
        p.FirstName ,
        p.LastName ,
        DATEPART(yyyy, soh.OrderDate) AS [Year] ,
        DATEPART(mm, soh.OrderDate) AS [Month] ,
        SUM(sod.LineTotal) AS LineItemTotal
FROM    Person.Person AS p
        INNER JOIN Sales.Customer AS c ON c.PersonID = p.BusinessEntityID
        INNER JOIN Sales.SalesOrderHeader AS soh ON c.CustomerID = soh.CustomerID
        INNER JOIN Sales.SalesOrderDetail AS sod ON soh.SalesOrderID = sod.SalesOrderID
GROUP BY p.BusinessEntityID ,
        p.FirstName ,
        p.LastName ,
        DATEPART(yyyy, soh.OrderDate) ,
        DATEPART(mm, soh.OrderDate)
ORDER BY p.BusinessEntityID ,
        [Year] ,
        [Month] ;

The problem is that this query may use a lot of CPU or disk resources. How can we avoid that? Earlier I mentioned using summary jobs. The problem is that the summary jobs will only run on a schedule and require that we maintain some kind of maintenance history of each row – even if that maintenance history is just a modification timestamp. Wouldn’t it be easier if our summary report just updated itself automagically?

It can! Enter the indexed view.

An indexed view is a regular view that has a clustered index on it. By adding the clustered index the view is persisted to disk. The upside of persisting the indexed view to disk is that there’s an automatic mechanism in place that will update the indexed view. An indexed view must have a unique clustered index so that SQL Server can identify the materialized row that must be updated in response to changes in the underlying data.

CREATE VIEW dbo.my_indexed_view
WITH SCHEMABINDING
AS
  SELECT  p.BusinessEntityID,
          p.FirstName,
          p.LastName,
          DATEPART(yyyy, soh.OrderDate) AS [Year],
          DATEPART(mm, soh.OrderDate) AS [Month],
          SUM(sod.LineTotal) AS LineItemTotal,
          COUNT_BIG(*) AS NumberOfRecords
  FROM    Person.Person AS p
          INNER JOIN Sales.Customer AS c ON c.PersonID = p.BusinessEntityID
          INNER JOIN Sales.SalesOrderHeader AS soh ON c.CustomerID = soh.CustomerID
          INNER JOIN Sales.SalesOrderDetail AS sod ON soh.SalesOrderID = sod.SalesOrderID
  GROUP BY p.BusinessEntityID,
          p.FirstName,
          p.LastName,
          DATEPART(yyyy, soh.OrderDate),
          DATEPART(mm, soh.OrderDate) ;
GO

CREATE UNIQUE CLUSTERED INDEX CX_my_indexed_view ON dbo.my_indexed_view (BusinessEntityID, [Year], [Month]) ;

We could have accomplished this using a summary table and triggers. Triggers are synchronous and updating the summary table will block our data changes from completing. In addition, if we ever want to bypass the trigger from updating (say during a huge data load) we need to disable and then enable the trigger again and then remember to update the summary table. With an indexed view we can just drop the clustered index and then recreate it when we’re done with our load processes.

Note: I can’t take full credit for this question and explanation. A SQL Saturday #42 attendee sent me an email with a question about using indexed views instead of triggers.

Comments

2 Comments so far. Comments are closed.
  1. One thing I’m curious about is your take on performance, where these are concerned. From what I have seen in the past, on the handful of systems I’ve implemented these on, is that update/insert/deletes to the underlying tables gets a whole lot slower, especially in the case of large bulk loads to those tables. We had a system where we implemented about a half-dozen indexed views, and the nightly load job runtimes went from 20 minutes to over 3 hours. Have you seen this type of behavior with indexed views?

    Thanks,
    -David.

    • Hi David -

      I’ve normally only used indexed views on tables that don’t get a lot of insert/update/delete activity since they do result in a lot of disk overhead. If there are bulk loads going on, it’s easy to drop the clustered index on the view and then rebuild it after the bulk load is complete. Other queries will suffer, but it will keep the nightly bulk loads running quickly. I couldn’t imagine that a clustered index rebuild on the view would take 2hours and 40 minutes, so that would be the best option in the one case you mentioned.

      Otherwise – indexed views do cause read/write overhead and can cause performance problems if there is a lot of data modification going on in the source tables. If that’s the case, an alternative is to use stored procedures or views and set up indexes to speed up view querying.

      There are a lot of different ways you can solve this problem. Feel free to email me if you want to chat about it more. There’s contact info over here.

This site is protected with Urban Giraffe's plugin 'HTML Purified' and Edward Z. Yang's Powered by HTML Purifier. 531 items have been purified.