Updating a table

Question

Updating a table

jmccary

Mr or Mrs. 500

Points: 541
More actions
November 20, 2003 at 9:20 am

#160918

I have created a View with a clustered index. The View contains about 4.5 million rows. It works great.
However, one of the columns (a tinyint but only 0 or 1 is used now) in the view needs periodic updating based on lots of factors that occur in other tables. Instead of using triggers, in the past (and before the view), I have just run a relative simple query once a week on the base tables that first set all the rows to 0 and then updated some rows to 1. This query took 1-2 minutes to run BEFORE I added the view. Now, it takes hours and I'm not quite sure how to approach this problem. I'm assuming the problem is related to the View being 'updated' also.
Any help or suggestions appreciated.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

mia Right there with Babe Points: 749 More actions · Answer 1

If you drop the index on the view before running the update query, does this reduce the time taken? Does the column you are updating participate in the clustered index on the View, which would mean that when it is updated, the row has to be physically moved? If so, could you remove this column from the clustered index on the view & see if that made a difference to the time taken for the update?

This may not make any difference but you could also try adding a where clause to the initial update of all rows to 0 & only update rows to 0 if they are not already 0. At least this would reduce the set of data being processed.

I don't have direct experience of this problem so these are just thoughts to float

Cheers,
mia

Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.

jmccary Mr or Mrs. 500 Points: 541 More actions · Answer 2

This table is in use 24/7 and dropping the index obviously requires rebuilding the index which in the past takes about 10 minutes and during which time, the view is basically inaccessible. The column I am updating does not participate in the clustered index.

You are absolutely correct about updating the zeroes. Thank you 🙂 Sometimes all those trees really get in the way!!

Any other suggestions?

mia Right there with Babe Points: 749 More actions · Answer 3

Afraid for the moment I've exhausted my ideas - indexed views are definitely not my are of expertise, but I'll let you know if I have a flash of inspiration. Sorry previous suggestions weren't of more use!

Cheers,
mia

Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.

bdohmen SSCrazy Points: 2996 More actions · Answer 4

Can you submit examples of the table structures and your views and update statements. Is there a reason you can't use a case statement in the update? Then you only make one pass thru the data instead of two.

Brian

jmccary Mr or Mrs. 500 Points: 541 More actions · Answer 5

Yes, I can use a CASE statement and that's a good idea. The impression I'm getting now is that I need to address the sql update statement(s). When I initially asked this question, I was expecting a 'revelation' like 'oh, you need to do XYZ and then run your updates'.

Thanks all!! Any other general observations or suggestions about indexed views will be much appreciated though.

recycled Mr or Mrs. 500 Points: 579 More actions · Answer 6

http://www.databasejournal.com/features/mssql/article.php/2119721

cmore SSCrazy Points: 2452 More actions · Answer 7

All of the fields in your view are stored again as a table internally.

Could you perhaps remove that volatile column from the materialized view, and then create a wrapper view that adds it back in? Assuming there is a nice index to join it back in with, you may end up with almost the same speed increase as you do currently with the materialized view, but without the overhead from a lot of updates to that field.