Sunday, February 17, 2013

SQL Server Batch Update



Always we should take care of database server performance but sometimes you need to perform a huge update on your table/tables this will affect performance for concurrent users and they will complain about it.
For example if you have a table contains more than billion row and you want to update a column of this data and your database is accessible 24 hours 7 days a week it is difficult to run this code in simple update statement because a lot of data will be fetch to your memory and a lot of user data will be kicked off out of memory.
So the solution is to use batch update by divide your data in small batches, e.g divide it to update 1000 row at a time and stop for a moment and the update the next 1000 and so on, this will let other users to use share the server with you.
Sample code:
-- SQL update in batches of 1000
WHILE (1 > 1)
  BEGIN
    BEGIN TRANSACTION
    UPDATE TOP ( 1000 ) S
    SET    Price = Price * 1.08    
    IF @@ROWCOUNT = 0
      BEGIN
        COMMIT TRANSACTION
         BREAK
      END
    COMMIT TRANSACTION
    -- 2 second delay
    WAITFOR DELAY '00:00:02'
  END
GO
* the above code is updated version of a code that available online.
Written by: Elmozamil Elamir Hamid

7 comments:

  1. I respect this blog to percentage statistics about this vital difficulty matter. right right here i discovered one of a kind segments and now i am going to use those new guidelines with new enthusiasm.

    Windows Server Standard

    ReplyDelete
  2. Wow, absolutely fantastic blog. I am very glad to have such useful information.

    ทองดีฟันขาว

    ReplyDelete
  3. It verbalizes a limitless imagination. I am totally impressed with the factual transformation skill of author used for transforming his thoughts and presented here. Impressed with deep meaning of this article.HPE ProLiant DL180 Gen9

    ReplyDelete
  4. Sometimes we have to face a lot of confusion when we found different opinion of different hundreds of articles related to same question. But I think now I am close to resolve my doubts after reading this blog.lenovo server

    ReplyDelete
  5. Impressive and powerful suggestion by the author of this blog are really helpful to reduce our hack-tic life. My own views are matching with author and I have experienced such.Acer Altos R380 F3

    ReplyDelete
  6. It is worthwhile reading this blog. I was searching such kind of blog for a long time but now I think I got a blog of my interest. I am thankful for these all suggestions mentioned under this blog.HPE ProLiant DL380 Gen9

    ReplyDelete

Card