Post reply

Long Running Update

  • February 7, 2007 at 4:20 pm

    #116201

    I am running an update to a table of 23.5 million rows with 23 indexes. The update is fairly starightforward. I'm adding a '1' to the end of a column. The column being updated is the second column of the table's primary key.

    update table1 set column_B = Column_X + column_Y

    Column_X has the same data as colum_B. Column_Y contains '1'.

    The update actually finished. Total run time was about 48 hours. But, I had to break the update into smaller updates using "like" to work with a range of rows at a time.  The like statement was against the second column of the primary key. So, I'm sure it didn't take advantage of the main table index.

    Besides the time that it took to run the update, the SQL server log kept filling up. I ended up running a shrinkfile on the log after each of my update statements.

    I'm sure that I did plenty wrong. Do you have any thoughts as to how I can make this update run faster? Or, can I accomplish the same results withou doing an update? I'd like to at least get the run time down to around eight hours. Thanks for your help.

  • February 7, 2007 at 4:26 pm

    #688424

    I think I would have:

    1. dropped the primary key

    2. ran the update

    3. recreated the primary key

    I BELIEVE what happened is that, since it was part of a primary key, before a value could be updated it had to check ALL the other rows to make sure the primary key wasn't violated. I think that if you had dropped the primary key and then done the update, it would have been quicker and would not have created such a large log file.

    -SQLBill

  • February 7, 2007 at 9:33 pm

    #688449

    Not only do what SQLBill suggested, but why 23 indexes?  And, are any of them clustered?  Are any of them affected by the update?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • February 7, 2007 at 10:03 pm

    #688452

    I would start from another question:

    why to do such UPDATE at all?

    Concatenating values from 2 fields contradicts essential rules of data management.

    If somebody some time needs concatenated values just use Column_X + column_Y in SELECTs.

    Non-normalised table with 23.5 million rows - that's the perfect way to discredit yourself as professional and the technology used by SQL Server.

    _____________
    Code for TallyGenerator

  • February 8, 2007 at 6:35 am

    #688531

    Yep, I agree with Serqiy about the denormalized thing... this is the wrong kind of update... and, if doing the concatenation in SELECTs turns out to be a chore, just add a calculated column. 

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 5 posts - 1 through 5 (of 5 total)

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