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:
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:
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.
OK, let me rollback this transaction:
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.
In the first indexed view will are going to group rows based on ProductID:
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.
To reset our time environment, we need to roll back the active transaction and drop this view:
The second index view groups rows based on SpecialOfferID:
Test is of course the same as the previous one — note that the total cost increased to 2.05.
Finally, let me test the performance impact on the UPDATE introduced by the indexed view in which the rows are grouped by SalesOrderID:
This time the cost is 2.21 so this kind of indexed views introduced almost 25% UPDATE performance penalties.
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