SQL Server Performance

UPDATE efficiency

Discussion in 'SQL Server 2005 General Developer Questions' started by rogilvie, Sep 25, 2007.

  1. rogilvie New Member

    Hi folks, I'm looking to improve the efficiency of an UPDATE statement that I currently run. Having read some previous discussions, I understand that it is best to break up a large UPDATE statement into smaller versions. Thus I am currently using code of the form:

    DECLARE @rowcount int
    SET @rowcount = 10000
    SET rowcount 10000
    WHILE @rowcount = 10000
    BEGIN
    BEGIN TRAN
    UPDATE my_view_v
    .
    WHERE <conditions>
    SET @rowcount = @@rowcount
    COMMIT TRAN
    END
    My question is: what is the best size of batch to use when updating? In my example I have used 10,000 records at a time, but was wondering if there is a conventional choice of batch size? I imagine it would be dependent on the size of the object you wish to update (my view has about 10M)
    Thanks
  2. FrankKalis Moderator

    That's true to a certain degree. You have to experiement here a little bit. The typically recommended (or mentioned) batch size is somewhat in the range of 5-15k rows.
  3. Madhivanan Moderator

    Well. Other than that

    WHILE @rowcount = 10000
    should be

    WHILE @rowcount >0
  4. satya Moderator

    Also you should keep in mind that transaction log backups during these UPDATE operations to keep up the size.
  5. rogilvie New Member

    Thanks for all the responses.
    As for the WHILE statements, I'm fairly sure they are equivalent - perhaps my version will have one less iteration.
  6. martins New Member

    Hi,
    Doing it in a loop (as you are) is definitely the best way. I have found that anything up to 100k rows at a time works well enough with tables larger than 100 million. This might differ depending on how many columns you update and on your hardware of course.
    You might also want to drop non-clustered indexes before updating.

Share This Page