Doing large updates against indexed view

Question

We have an indexed view that runs across three large tables. Two of these tables (A & B) are constantly getting updated with user transactions and the other table (C) contains data product info that is needs to be updated once a week. This product table contains over 6 million records.

We need this view across these three tables for our core business process and unfortunately we cannot change this aspect. We even had a sql server MVP come in to help test under load to make sure we have the most efficient configuration. There is one column in the product table that gets utilized in the view and has to be updated each week.

The problem we are now encountering is that as volume is increasing on our transactions against tables A & B, the update to Table C is causing deadlocks.

I have tried several different methods to no avail: 1) I was hoping that we could change the view so that table C could be a dirty read "WITH (NOLOCK)" but apparently that functionality is not available with indexes views.

2) I thought about updating a new column in Table C and then just renaming it when the process is done but you cannot do that due to the dependency in the view.

3) I also entertained the idea of writing this value to a temporary product table, and then running an ALTER statement against the view to have it point to my new table. however when i did that the indexes on my view were dropped and it took quite a bit of time to recreate them.

4) we tried to do the weekly update in small chunks (as small as 100 records at a time) but we still run into dead locks.

questions:

a) we are using sql server 2005. Does sql server 2008 have a new functionality with their indexed views that would help us? Is there now a way to do dirty reads w/ an indexed view?

b) a better approach to altering an existing view to point to a new table?

thanks!

Answer 1

Have you considered making C a partitioned table and swapping in/out a partition as your price update mechanism? I'm not sure how that would work with an indexed view - I would think the index needs to be rebuilt at that point. I think this is probably the same situation you are seeing with the ALTER TABLE, actually.

Is the indexed view really necessary? i.e. could appropriate indexes on the 3 underlying tables perform just as well when a normal view is used? Remember that the indexed view may have to be updated on key changes to any of the three tables, while a index on a single table would only have to be updated if a key changes or data moves in just that table. Typically indexed views are indexed on different columns than the base tables because it is a different kind of section accross the data than is available in the underlying tables - does that description really apply?

How long does the pricing update take? This would appear to be the core of your problem, but it's hard to say without more information.

Answer 2

The issue you're experiencing is that adding the indexed view between the three tables is causing lock contention. There is a really good post about the issue here : http://sqlblog.com/blogs/alexander_kuznetsov/archive/2009/06/02/be-ready-to-drop-your-indexed-view.aspx

Partitioning the table might provide some relief, although I don't know if the partitioning will circumvent the lock issue. You will have to upgrade to 2008 if you want to investigate this option however - as you need to use partition-aligned indexed views. 2005 will require you to drop the view before you swap in/out any partitions.

More information about partition-aligned indexed views: http://msdn.microsoft.com/en-us/library/dd171921.aspx