Indexed Views. Part 2. The Costs

In the first part of this short series (Indexed Views. Part 1. The Benefits) we saw that an indexed view can reduce the grouping query cost by a factor of 500 hundred or more. Now it’s time to compare this huge gain with the additional cost associated with every data modification.

How costly an indexed view can be?

To answer this question we will need a copy of SalesOrderDetail table —this way we will assure that no triggers are associated with our test table and that we are actually measuring the cost of a single update statement:

CREATE DATABASE  TestDB
GO
USE TestDB;
GO

SELECT *
INTO [SalesOrderDetail] 
FROM [AdventureWorks2008R2].[Sales].[SalesOrderDetail];
GO
CREATE UNIQUE CLUSTERED INDEX PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID
ON [SalesOrderDetail](SalesOrderID, SalesOrderDetailID);
GO

 

Let’s update some rows. The BEGIN TRAN statement is used to give us the ability to rollback this transaction afterwards — this way the same test can be repeated many times:

BEGIN TRAN
UPDATE [SalesOrderDetail] 
SET [UnitPrice] = [UnitPrice]+1
WHERE [ProductID] % 10 = 0;
GO

 

The base cost of updating 10% rows in this table is 1.82. And as you can see, the execution plan is really a simple one: SQL Server found the required rows and then updated them.

image

OK, let me rollback this transaction:

ROLLBACK
GO

 

If you remember, in the first part I said that before adding an indexed view you should check how many groups will be stored in it and how many rows will be used to calculate values for each of those groups. In this table there are three columns with very different density (the total number of rows is 121317):

1. SpecialOfferID has only 12 distinguished values , so if we used this column in GROUP BY clause, there would be only 12 groups with more the 10 000 rows in each.

2. There are 266 different ProductIDs

3. And 31465 distinct SalesOrderIDs .

Our task is to compare the cost of an indexed views that are based on those three columns.

SELECT COUNT (DISTINCT [ProductID]) AS ProductIDs, COUNT (DISTINCT [SpecialOfferID]) AS SpecialOfferIDs,COUNT (DISTINCT [SalesOrderID]) AS SalesOrderIDs, COUNT(*) AS Rows
FROM  [SalesOrderDetail];
GO
-----
ProductIDs    SpecialOfferIDs    SalesOrderIDs    Rows
266            12                31465            121317

 

In the first indexed view will are going to group rows based on ProductID:

CREATE VIEW [_SumUnitPriceByProducts]
WITH SCHEMABINDING
AS
SELECT [ProductID], SUM([UnitPrice]) AS TotalPrice, COUNT_BIG(*) AS NumberOfProducts
FROM [dbo].[SalesOrderDetail]  
GROUP BY [ProductID];
GO
CREATE UNIQUE CLUSTERED INDEX Idx1
ON [_SumUnitPriceByProducts] ([ProductID]);
GO

 

Now the execution plan is quite different, but the total cost did not grow as much as it may look like — now the same update costs us 2.03. Note that table spool was added to efficiently correlate rows from a based table with rows that are stored in the indexed view.

BEGIN TRAN
UPDATE [SalesOrderDetail] 
SET [UnitPrice] = [UnitPrice]+1
WHERE [ProductID] % 10 = 0;
GO

 

image

To reset our time environment, we need to roll back the active transaction and drop this view:

ROLLBACK
GO

DROP VIEW [_SumUnitPriceByProducts];
GO

 

The second index view groups rows based on SpecialOfferID:

CREATE VIEW [_SumUnitPriceBySpecialOffer]
WITH SCHEMABINDING
AS
SELECT [SpecialOfferID], SUM([UnitPrice]) AS TotalPrice, COUNT_BIG(*) AS NumberOfProducts
FROM [dbo].[SalesOrderDetail]
GROUP BY [SpecialOfferID];
GO
CREATE UNIQUE CLUSTERED INDEX Idx1
ON [_SumUnitPriceBySpecialOffer] ([SpecialOfferID]);
GO

 

Test is of course the same as the previous one — note that the total cost increased to 2.05.

image

Finally, let me test the performance impact on the UPDATE introduced by the indexed view in which the rows are grouped by SalesOrderID:

CREATE VIEW [_SumUnitPriceBySalesOrder]
WITH SCHEMABINDING
AS
SELECT [SalesOrderID], SUM([UnitPrice]) AS TotalPrice, COUNT_BIG(*) AS NumberOfProducts
FROM [dbo].[SalesOrderDetail] 
GROUP BY [SalesOrderID];
GO
CREATE UNIQUE CLUSTERED INDEX Idx1
ON [_SumUnitPriceBySalesOrder] ([SalesOrderID]);
GO

 

This time the cost is 2.21 so this kind of indexed views introduced almost 25% UPDATE performance penalties.

image

Lesson learned — there is always an additional cost associated with an indexed view. But this cost varies greatly — in our simple case the UPDATE was slower from about 10% (in the base case scenario) to almost 25% (in the worst case one). Be careful and always check the number of distinct values (groups) before adding an indexed view to your database.

See you next time

Share this article ...

Google Plus
Ihren LinkedIn Kontakten zeigen



This entry was posted in Administration, Performance and tagged by Marcin Szeliga. Bookmark the permalink.
Marcin Szeliga

About Marcin Szeliga

Since 2006 invariably awarded Microsoft Most Valuable Professional title in the SQL category. A consultant, lecturer, authorized Microsoft trainer with 15 years’ experience, and a database systems architect. He prepared Microsoft partners for the upgrade to SQL Server 2008 and 2012 versions within the Train to Trainers program. A speaker at numerous conferences, including Microsoft Technology Summit, SQL Saturday, SQL Day, Microsoft Security Summit, Heroes Happen {Here}, as well as at user groups meetings. The author of many books and articles devoted to SQL Server.

Leave a Reply

Connect with:
  • This field its required.
  • This field its required.
    • Message is required