title: Performance for RBAR vs. set-based processing with varying transactional sizes
body: <p>It is conventional wisdom that set based processing of tables should always be preferred over RBAR - especially when the tables grow larger and/or you need to update many rows.</p>
  
  <p>But does that always hold? I have experienced quite a few situations - on different hardware - where set-based processing shows exponential growth in time consumption, while splitting the same workload into smaller chunks gives linear growth.</p>
  
  <p>I think it would be interesting either to be proven totally wrong - if I'm missing something obvious - or, if not, it would be very good to know when splitting the workload is worth the effort. And subsequently identifying what indicators help make the decision of which approach to use. I'm personally expecting the following components to be interesting:</p>
  
  <ul>
  <li>Size of workload </li>
  <li>Size and growth of logfile</li>
  <li>Amount of RAM </li>
  <li>Speed of disksystem </li>
  </ul>
  
  <p>Any other? Number of CPUs/CPU cores? </p>
  
  <p>Example 1: I have a 12 million row table and I have to update one or two fields in each row with data from another table. If I do this in one simple UPDATE, this takes ~30 minutes on my test box. But I'll be done in ~24 minutes if I split this into twelve chunks - ie.: </p>
  
  <pre><code>WHERE &lt;key&gt; BETWEEN 0 AND 1000000
  WHERE &lt;key&gt; BETWEEN 1000000 AND 2000000
  ...
  </code></pre>
  
  <p>Example 2: Is a 200+ million rows table that also need to have several calculations done to practically all rows. If a do the full set all in one, my box will run for three days and not even then be done. If I write a simple C# to execute the exact same SQL, but with WHERE-clauses appended to limit transaction size to 100k rows at a time, it'll be done in ~14 hours.</p>
  
  <p>For the record: My results are from the same databases, resting on the same physical hardware, with statistics updated, no changes in indexes, Simple recovery model, etc.</p>
  
  <p>And no, I haven't tried 'true' RBAR, although I probably should - even though it would only be to see how long that would really take.</p>