Doing large updates against indexed view
- by user217136
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!
