title: Bulk Updates Concept for SQL and VB.net winform
body: <p>I would appreciate some ideas, discussion and feasibility of same, regarding  bulk updates on a large table in SQL Server 2008+.</p>
  
  <p>Currently I have a table with 10,000 rows and 160 columns. This table is updated very frequently with 1 to 100+ column changes per row depending on the process. Using the 'standard newbie' table update using a <code>DataAdapter</code> is very slow and unsuitable.</p>
  
  <p>The quest is to find a faster way. I have tried fine-tuning the <code>DataAdapter.Update</code> with batch size, regardless the more heavy updates take 10-15 seconds. In the meanwhile <code>SqlBulkCopy</code> imports the whole table in (ball park) 1-3 seconds. When the update procedure takes place 30-50 times in a process the 10s-15s add up! </p>
  
  <p>Being internet self thought, I have gaps in my experience, however there are 2 possibilities that I can think of that may be better at accomplishing the task of the update.</p>
  
  <ol>
  <li><p>Dump the table content from the database and repopulate the table using <code>SqlBulkcopy</code>.</p></li>
  <li><p>Using a stored procedure with a table passed to it with a merge SQL statement.</p></li>
  </ol>
  
  <p>The main issue is data safety, although this is a local single user application there needs to be a way to handle errors roll back. From my understanding the dump and replace would be simpler, but perhaps more prone to data loss? The stored procedure would be far more extensive to set up as the update statement would have to have all the update columns typed individually and maintained for changes. Unless there is one 'Update *' statement :).  </p>
  
  <p>In trying to keep this short I want to keep this at a concept level only , but will appreciate any different ideas or links and advice.</p>
  
  <p>EDIT further info: 
  The table has only one index, the ID column. Its a simple process of storing incoming (and changing) data to a simple datatable. and the update can be anywhere between 1 row to 1000 rows. The program stores the information to the database very often, and can be some or nearly all the columns. Building a stored procedure for each update would be impossible as I don't know which data will be updated, you can say that all of the columns will be updated (except the ID column and a few 'hard' data columns) it depends on what the update input is. So there is no fine tuning the update to specific columns unless I list nearly all of them each time. In which case one stored procedure would do it.</p>
  
  <p>I think the issue is the number of 'calls' to the database are made using the current data adapter method.</p>
  
  <p>EDIT:
  3 WHat about a staging table where I bulk copy the data to and then have a store procedure do the update. Wouldn't that cut down the SQL trafic? I think that is the problem with the dataadapter update.</p>
  
  <p>Edit: Posted an atempt of concept 1 in an answer to this thread.</p>
  
  <p>Thank you </p>