Smart questions
Smart answers
Smart people
Join Tek-Tips Forums
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

Microsoft SQL Server: Programming FAQ

SQL Server Performance Issues

How to Use Batch Size to Speed Mass Updates, Inserts and Deletes
Posted: 7 Feb 03 (Edited 7 Feb 03)

It is often necessary to perform mass updates, inserts, or deletes on a table or tables. If the action involves thousands to millions of rows, performance can bog down and the transaction log can grow very large, consuming much available disk space. In fact, updating millions of rows in one query may not even complete because the transaction log fills all available disk space and can no longer grow. Another problem occurs when log growth cannot not keep up with the space needed for the update. SQL will often error indicating the log is full when in fact there is plenty of disk space.

It is important to understand why this happens. SQL Server must keep all transactions in the transaction log until they are committed. This is necessary for rollback to occur in the event of an error. When thousands or millions of rows are updated in one transaction, all the information required for rollback is retained in the transaction log until the transaction commits or rolls back. Storing millions of rows in the log can cause it to grow large.

One way to overcome this problem and greatly improve performance of mass update queries is to perform the updates, inserts or deletes in smaller batches. Each batch should be enclosed in a transaction and the transaction committed after the specified number of rows have been processed. This is the same technique used by the DTS bulk insert task when the BatchSize property is set. (BTW, I highly recommend setting the BatchSize property when importing very large files into SQL Server tables.)

Here are samples of updates, deletes and inserts in batch transactions. In each example, the batch size is set at 50K. Another value may be optimal for different queries.

--Set UpdFlag in MyTable to 0 where KeyCol matches
--primary key in ControlTable and UpdFlag not = 0.

--Set rowcount to 50000 to limit number of updates
--performed in each batch to 50K rows.
Set rowcount 50000

--Declare variable for row count
Declare @rc int
Set @rc=50000

While @rc=50000
 Begin

  Begin Transaction

  --Use tablockx and holdlock to obtain and hold
  --an immediate exclusive table lock. This unusually
  --speeds the update because only one lock is needed.
  Update MyTable With (tablockx, holdlock)
    Set UpdFlag = 0
  From MyTable mt
  Join ControlTable ct
    On mt.KeyCol=ct.PK
  --Add criteria to avoid updating rows that
  --were updated in previous pass
  Where m.UpdFlag <> 0

  --Get number of rows updated
  --Process will continue until less than 50000
  Select @rc=@@rowcount

  --Commit the transaction
  Commit
 End

--Delete rows older than 90 days

--Set rowcount to 50000 to limit number of deletes per batch
Set rowcount 50000

--Declare variable for row count
Declare @rc int
Set @rc=50000

While @rc=50000
 Begin

  Begin Transaction

  --Use tablockx and holdlock to obtain and hold
  --an immediate exclusive table lock. This unusually
  --speeds the update because only one lock is needed.
  Delete MyTable With (tablockx, holdlock)
  Where InsertDate < dateadd(d,-90,getdate())

  --Get number of rows updated
  --Process will continue until less than 50000
  Select @rc=@@rowcount

  --Commit the transaction
  Commit
 End

-----------------------------------------------------

--Insert rows into MyTable from ImportTable
--where key value doesn’t already exist.

--Set rowcount to 50000 to limit number of inserts per batch
Set rowcount 50000

--Declare variable for row count
Declare @rc int
Set @rc=50000

While @rc=50000
 Begin

  Begin Transaction

  --Use tablockx and holdlock to obtain and hold
  --an immediate exclusive table lock. This usually
  --speeds the insert because only one lock is needed.
  Insert MyTable (KeyCol, Col1, Col2, Col3) With (tablockx, holdlock)
  Select i.ID, i.ColA, i.ColB, i.ColC
  From ImportTable i
  Left Join MyTable m
    On i.ID=m.KeyCol
  Where m.KeyCol Is Null

  --Get number of rows updated
  --Process will continue until less than 50000
  Select @rc=@@rowcount

  --Commit the transaction
  Commit
 End

Back to Microsoft SQL Server: Programming FAQ Index
Back to Microsoft SQL Server: Programming Forum

My Archive

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close