title: Performance of Multiple Parallel (async) SqlBulkCopy inserts, against different tables, in a single Transaction
body: <h1>TL;DR</h1>
  <p><strong>Why does running multiple SqlBulkCopy inserts, against unrelated tables, <code>async</code> &amp; in parallel, on a single Transaction seem to behave as though it's running in series instead?</strong></p>
  <hr>
  <h1>Context</h1>
  <p>I have some code that is calculating and storing a large volume of data.
  The calculation is done up-front, so the storage section of the code gets handed this big pile of data to be stored.</p>
  <p>My DB writes are being done with <code>SqlBulkCopy.WriteToServerAsync</code> which does the job nicely, in general.</p>
  <p>Amongst the things I need to store are 6 tables which are business-related, but not SQL-related. As such, my write to them needs to be in a transaction, so that an error on any one write reverts the writes on all the others.</p>
  <p>The performance of this code is fairly critical, so I want to be able to run the BulkInserts in parallel. There are no FKeys or any other tables being interacted with, (data integrity is managed by the code) so I don't see any reason that this shouldn't be possible.</p>
  <hr>
  <h1>What I've currently written</h1>
  <p>I thought I knew how to write all the code and have been able to get it all working, but there's a weird performance slow-down that I don't understand:</p>
  <p><em>Happy to provide actual bits of code you want, but this is already a very long Q, and the code would be pretty long to0. LMK if you do want to see anything.</em></p>
  <p>I can write:</p>
  <ul>
  <li><p>&quot;BulkInsert into each table <strong>sequentially</strong>, all in a <strong>single Transaction</strong>&quot;.</p>
  <ul>
  <li>i.e. I open a <code>new SqlConnection()</code> and <code>.BeginTransaction()</code>,</li>
  <li>then I <code>foreach</code> over the 6 tables, and <code>await InsertToTable(transaction)</code> each table before the <code>foreach</code> moves to the next one.</li>
  <li>When the <code>foreach</code> concludes then I <code>.Commit()</code> the transaction and close the connection.</li>
  <li>I have a large-volume test that runs this version in <strong>184</strong> seconds (95%, +/- 2.45s).</li>
  </ul>
  </li>
  <li><p>&quot;BulkInsert into each table <strong>sequentially</strong>, with a <strong>new connection &amp; Transaction for each table</strong>.&quot;</p>
  <ul>
  <li>i.e. I <code>foreach</code> over the 6 tables, and <code>await InsertToTable()</code> each table before the <code>foreach</code> moves to the next one.</li>
  <li>Inside each <code>InsertToTable()</code> call I open a new <code>SqlConnection</code> and <code>BeginTransaction</code>, and then I <code>.Commit()</code> and <code>.Close()</code> before returning from the method.</li>
  <li>I have a large-volume test that runs this version in <strong>185</strong> seconds (95%, +/- 3.34s).</li>
  </ul>
  </li>
  <li><p>&quot;BulkInsert into each table in <strong>parallel</strong>, with a <strong>new connection &amp; Transaction for each table</strong>.&quot;</p>
  <ul>
  <li>i.e. I initiate all 6 of my tasks by calling <code>thisTableTask = InsertToTable()</code> for each table, and capturing the <code>Task</code>s but <em>not</em> <code>await</code>ing them (yet).</li>
  <li>I <code>await Task.WhenAll()</code> the 6 tasks captured.</li>
  <li>Inside each <code>InsertToTable()</code> call I open a new <code>SqlConnection</code> and <code>BeginTransaction</code>, and then I <code>.Commit()</code> and <code>.Close()</code> before returning from the method. (but note that the foreach has moved onto the next table, because it doesn't <code>await</code> the Task immediately.</li>
  <li>I have a large-volume test that runs this version in <strong>144</strong> seconds (95%, +/- 5.20s).</li>
  </ul>
  </li>
  <li><p>&quot;BulkInsert into each table in <strong>parallel</strong>, all in a <strong>single Transaction</strong>&quot;.</p>
  <ul>
  <li>i.e. I open a <code>new SqlConnection()</code> and <code>.BeginTransaction()</code>.</li>
  <li>Then I initiate all 6 of my tasks by calling <code>thisTableTask = InsertToTable(transaction)</code> for each table, and capturing the <code>Task</code>s but <em>not</em> <code>await</code>ing them (yet).</li>
  <li>I <code>await Task.WhenAll()</code> the 6 tasks captured.</li>
  <li>Once the <code>WhenAll</code> concludes then I <code>.Commit()</code> the transaction and close the connection.</li>
  <li>I have a large-volume test that runs this version in <strong>179</strong> seconds (95%, +/- 1.78s).</li>
  </ul>
  </li>
  </ul>
  <p>In all cases the eventual BulkInsert looks like:</p>
  <pre class="lang-cs prettyprint-override"><code>using (var sqlBulk = BuildSqlBulkCopy(tableName, columnNames, transactionToUse))
  {
      await sqlBulk.WriteToServerAsync(dataTable);
  }
  
  private SqlBulkCopy BuildSqlBulkCopy(string tableName, string[] columnNames, SqlTransaction transaction)
  {
      var bulkCopy = new SqlBulkCopy(transaction.Connection, SqlBulkCopyOptions.Default, transaction)
      {
          BatchSize = 10000,
          DestinationTableName = tableName,
          BulkCopyTimeout = 3600
      };
  
      foreach (var columnName in columnNames)
      {
          // Relies on setting up the data table with column names matching the database columns.
          bulkCopy.ColumnMappings.Add(columnName, columnName);
      }
  
      return bulkCopy;
  }
  
  </code></pre>
  <hr>
  <h1>Current Performance stats</h1>
  <p>As listed above</p>
  <ul>
  <li>Sequential + single Tran = 184s</li>
  <li>Sequential + separate Trans = 185s</li>
  <li>Parallel + separate Tran = 144s</li>
  <li>Parallel + single Tran = 179s</li>
  </ul>
  <p>Those first 3 results all make sense to me.</p>
  <p>#1 vs #2: As long as the inserts all work, the Transactions don't do much. The DB is still doing all the same work, at the same points in time.</p>
  <p>#2 vs #3: This was the entire point of running the inserts in parallel. By running the inserts in parallel, we spend less time waiting around for SQL to do it's thing. We're making the DB do a lot of work in parallel, so it's not as much as a 6-fold speed up, but it's still plenty.</p>
  <hr>
  <h1><strong>QUESTION:</strong></h1>
  <p><strong>Why is the last case so slow?</strong> And can I fix it?</p>
  <ul>
  <li>Parallel + single Tran = 179</li>
  </ul>
  <p>That's almost as slow as doing it in series, and fully 25% slower than doing it in parallel, but with multiple transactions!</p>
  <p>What's going on?
  Why does running multiple SqlBulkCopy inserts, against unrelated tables, <code>async</code> &amp; in parallel, on a single Transaction seem to behave as though it's running in series instead?</p>
  <hr>
  <h1>Non-Dupes:</h1>
  <p><a href="https://stackoverflow.com/questions/15471177/sqlbulkcopy-multiple-tables-insert-under-single-transaction-or-bulk-insert-opera">SqlBulkCopy Multiple Tables Insert under single Transaction OR Bulk Insert Operation between Entity Framework and Classic Ado.net</a> (Isn't running the queries in parallel)</p>
  <p><a href="https://stackoverflow.com/questions/37188875/using-sqlbulkcopy-in-one-transaction-for-multiple-related-tables">Using SqlBulkCopy in one transaction for multiple, related tables</a> (Tables are related and they're trying to read back out of them)</p>
  <p><a href="https://stackoverflow.com/questions/9519402/parallel-bulk-inserting-with-sqlbulkcopy-and-azure">Parallel Bulk Inserting with SqlBulkCopy and Azure</a> (that's talking about parallel load into a <em>single</em> table)</p>