Post reply

Best way to do an Update Statement - Advice needed

  • October 21, 2009 at 10:30 pm

    #135791

    I am going to update three table each containing 30 million records

    The query i used is like this

    BEGIN TRY

    BEGIN Tran

    update ...........

    update ............

    update ..........

    Commit tran

    END TRY

    BEGIN CATCH

    ROLLBACK TRAN

    END CATCH

    It took five hours to update , so i need an better way of doing it

    and it took 45 gb of my log

    The update statement i straight no issues with query

    Kinldy help it out

  • October 22, 2009 at 12:03 am

    #1069243

    To reduce logging and gain speed, you are probably going to wind up setting your db to simple recovery mode and then updating the tables in batches, rather than all at once. You might benefit from reading this article:

    http://qa.sqlservercentral.com/articles/Miscellaneous/2954/

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

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

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