December 3, 2003 at 5:17 am
I have a View with a clustered index with 4 million rows. It takes about 10 minutes to create this view. Once a day at off-peak times, I do multiple updates/inserts to one of the view's base tables. During this time, the performance of the view basically comes to a halt.
I've thought about
-creating a 'real table' of the view
- drop the view (and use the 'real table' for the 10 minute period)
- do all the updates/inserts on the base tables
- recreate the view
Any suggestions appreciated
John
December 9, 2003 at 12:00 pm
This was removed by the editor as SPAM
December 10, 2003 at 8:52 am
Could this be as simple as a locking issue? I'm not sure how views operatr under the hood, but one would suppose that, if activity has a base table locked, the view would be locked too (even if all the data needed resides in the clustered index, the base table is unreadable because it is in a state of flux, so the system should know that this status should extend to the view as well, I would hope).
Alternately, the time frame for the inserts and updates mentioned becomes a factor as well. If you are doing a bunch of changes as a single transaction, could they be done as smaller, discreet transactions? I have been able to process hundreds of thousands of updates to a 5 million row table without affecting performance by limiting my script to a maximum of 10,000 at a time, and by inserting a two second WAITFOR in between my update attempts. I can run this during our peak hours without impacting the performance of the system, and still get reasonable processing times (not as fast as if I could simply lock things down, but acceptable nonetheless).
A couple of items to think about, at least.
R David Francis
R David Francis
December 10, 2003 at 1:34 pm
For one thing, I was recently reminded that indexed views only work for the enterprise & developer editions. The other 2000 editions will accept the syntax, but not actually index the views. This explains lack of performance gain on index views for some folks.
Did you try checking the execution plan? Updating statistics? check for locks - use READ UNCOMMITTED or WITH (NOLOCK) if you can risk dirty reads. sql profiler can also help.
Data: Easy to spill, hard to clean up!
December 11, 2003 at 1:17 pm
Would it be possible to change the clustering strategy on your view? If you can build a unqiue clustered index which respons better to your updates/inserts, you can create your current clustered index as a non-clustered index.
Otherwise, you could drop all of your indexes on this view prior to performing your updates/inserts and then recreate them after. This would see preferable to the alternatives you listed.
December 11, 2003 at 1:17 pm
Would it be possible to change the clustering strategy on your view? If you can build a unqiue clustered index which respons better to your updates/inserts, you can create your current clustered index as a non-clustered index.
Otherwise, you could drop all of your indexes on this view prior to performing your updates/inserts and then recreate them after. This would see preferable to the alternatives you listed.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply