Post reply

Updating a table

  • 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.

  • November 20, 2003 at 9:41 am

    #482915

    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.

  • November 20, 2003 at 9:52 am

    #482920

    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?

  • November 20, 2003 at 10:02 am

    #482921

    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.

  • November 20, 2003 at 10:25 am

    #482922

    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

  • November 20, 2003 at 10:34 am

    #482925

    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.

  • November 20, 2003 at 11:45 am

    #482938

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

  • November 20, 2003 at 3:42 pm

    #482981

    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.

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

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