title: C# & SQL Server Insert Performance
body: <p>I've been working on a custom data layer for an application written in C# and I have a few concerns regarding performance.</p>
  
  <p>Note 1: This is not an attempt to perform bulk inserts but rather an attempt gauge the potential concurrent throughput. Using a bulk insert from whichever framework is not currently an option. If I were to do data imports - this would be the route I would take.</p>
  
  <p>I am doing a simple insert to a record about 2500 times to gauge the records inserted per-second. I reuse the same object for the insert each time to reduce the overhead here and I am now pre-generating the statement that actually does the insert. The pre-generated statement looks like this:</p>
  
  <pre><code>INSERT INTO "dbo"."account" ("accountnumber", 
    "address_city", "address_line1", "address_line2", "address_line3", 
    "address_postalcode", "address_province", "address_region", "name")
  OUTPUT INSERTED.id
  VALUES (@accountnumber, 
    @address_city, @address_line1, @address_line2, @address_line3, 
    @address_postalcode, @address_province, @address_region, @name);
  </code></pre>
  
  <p>My original data structure had used a <code>Dictionary&lt;string, object&gt;</code> and it passed through another helper function to apply the parameters with values to the DbCommand. To reduce this overhead I had converted it to a <code>Tuple&lt;string, object&gt;[]</code> instead. Both of these improvements, pre-generating the SQL statement for inserting and the Tuple object, increased the performance by about 20%. Which is fantastic; however, the baseline performance that being achieved was in the ballpark of around 80-100 record inserts per second so the improvement yielded around 120 records per second.</p>
  
  <p>To delve into this a little more - the first tests are entirely synchronous and all contained within one transaction. I had to break out the connection code in order to test this directly. In addition, one connection is being used. So a combination of all of these factors only yields 120 records/s is concerning whereas direct on the SQL server I can get 2500 records/s.</p>
  
  <p>For the next set of tests I do this entirely async within one transaction. The end result lands me in the 600-900 records/s range. Which is a huge improvement over the standard sync insert. I do understand that the sync nature of the original test would be slower but I didn't expect it to be this drastic of a change.</p>
  
  <p>Finally to my question. Should I pursue an application with the sync insert methods and retain an easier architecture to develop or should I use the async methods and find a way to make it work within the application? Are there any potential improvements that any of you, the audience, can recommend? Next is related to the transaction - should I use <code>TransactionScope</code> instead of creating the transaction from the <code>DbConnection</code>?</p>
  
  <p>This is what the async code looks like:</p>
  
  <pre><code>using (var dConn = dataContext.GetConnection())
  {
      using (var transaction = dConn.BeginTransaction())
      {
          var statement = ... code that pregenerates the sql insert statement ...
          var waitable = new List&lt;Task&lt;object&gt;&gt;();
  
          for (int i = 0; i &lt; records; i++)
          {
              var command = dConn.CreateCommand();
              command.CommandText = statement;
              command.Transaction = transaction;
              command.ApplyParameters(record);
  
              waitable.Add(command.ExecuteScalarAsync());
              count++;
          }
  
          while(true)
          {
              if (waitable.Where(t =&gt; t.IsCompleted).Count() == waitable.Count()) break;
              Console.WriteLine("Sleep 50ms ...");
              Thread.Sleep(50);
          }
  
          transaction.Commit();
      }
  }
  </code></pre>
  
  <p>This is what the sync code looks like:</p>
  
  <pre><code>using (var dConn = dataContext.GetConnection())
  {
      using (var transaction = dConn.BeginTransaction())
      {
          var statement = ... code that pregenerates the sql insert statement ...
          var command = dConn.CreateCommand();
          command.CommandText = statement;
          command.Transaction = transaction;
          command.ApplyParameters(record);
  
          for (int i = 0; i &lt; records; i++)
          {
              command.ExecuteScalar();
              count++;
          }
  
          transaction.Commit();
      }
  }
  
  sw.Stop();
  </code></pre>
  
  <p>[11/13/2016] Update:
  I've done some additional testing and eventually converted the core data layer of the application to .Net Core 1.0.1. Initial test results are very promising. I am getting around ~1500 records/s without transactions, ~4500 records/s with transactions and ~6500 records/s with async calls and transactions.</p>