title: Mass Updates and Commit frequency in SQL Server
body: <p>My database background is mainly Oracle, but I've recently been helping with some SQL Server work.  My group has inherited some SQL server DTS packages that do daily loads and updates of large amounts of data.  Currently it is running in SQL Server 2000, but will soon be upgraded to SQL Server 2005 or 2008.  The mass updates are running too slowly.  </p>
  
  <p>One thing I noticed about the code is that some large updates are done in procedural code in loops, so that each statement only updates a small portion of the table in a single transaction.  Is this a sound method to do updates in SQL server?  Locking of concurrent sessions should not be an issue because user access to tables is disabled while the bulk loading takes place.  I've googled around some, and found some articles suggesting that doing it this way conserves resources, and that resources are released each time an update commits, leading to greater efficiency.  In Oracle this is generally a bad approach, and I've used single transactions for very large updates with success in Oracle.  Frequent commits slow the process down and use more resources in Oracle.</p>
  
  <p>My question is, for mass updates in SQL Server, is it generally a good practice to use procedural code, and commit many SQL statements, or to use one big statement to do the whole update?</p>