Post reply

Update on a large table.

  • December 2, 2005 at 9:13 am

    #95966

    I have large fact table (30 million rows) which I need to run the following update statement. However my SQL Server doesn't seem to have the puff to be able complete it.

    Update Fact_Transaction

     Set Fact_Transaction.TRANSACTION_STATUS_SKEY = Dim_Transaction_Status.TRANSACTION_STATUS_SKEY

    From

     Fact_Transaction

      Inner Join Dim_Transaction_Status On

       Fact_Transaction.Entry_Status = Dim_Transaction_Status.Transaction_Status_Code

    I am testing the query on my dev environment and I have found that 24 hours after setting it off it is still running with the disk running at 100% useage.

    I experimented with using SET ROWCOUNT to limit the rows affected to 1 million records and the update takes about 2 minutes. Increasing the rowcount to 5 million takes about 20 mins and yet if I don't set the rowcount I have yet to see the query complete.

    Does anyone have any ideas on what I can try?

  • December 2, 2005 at 9:19 am

    #607415

    Hi,

    Did u set SET NOCOUNT ON. Does this have any effect


    Helen
    --------------------------------
    Are you a born again. He is Jehova Jirah unto me

  • December 2, 2005 at 9:29 am

    #607423

    I haven't used the SET NOCOUNT option. Does this have an effect on performance?

    Thanks, Daniel

  • December 2, 2005 at 9:36 am

    #607426

    What is the initial value of the column being updated ? Are they all initially NULL ?

    If so, add a:

    WHERE Fact_Transaction.TRANSACTION_STATUS_SKEY IS NULL

    And run with a RowCount of 5 million, in 6 batches until there's nothing left to update.

    Also, if possible, place the DB in simple logging mode while doing this to reduce log writes.

     

    [Edit] I don't think SET NOCOUNT has any impact on performance.

  • December 2, 2005 at 9:36 am

    #607425

    Hi,

    You can try that. It gives a definte boost.  However I prefer to use a batchjob. As always, try to minimize the amount of inserts, deletes and updates in one transaction. If there are any indexes try to disable them and then enable them.

    /*

    update records in short transactions of a 100000 records a time

    DECLARE @rc int

    WHILE 1 = 1

    BEGIN

    BEGIN TRANSACTION

    update statement

    SELECT @rc = @@ROWCOUNT

    COMMIT TRANSACTION

    IF @rc <> 100000 BREAK

    END

    */

    My two cents

     


    Helen
    --------------------------------
    Are you a born again. He is Jehova Jirah unto me

  • December 2, 2005 at 10:15 am

    #607438

    Unfortunately the field isn't NULL but populated with the wrong values. However I can probably hijack the status flag on the record for the purpose of the update, and create solution using ROWCOUNT, WHERE clause and a WHILE loop so I don't have to be around to press F5.

     

    Thanks for your help guys, much appreciated.

     

    Daniel

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

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