title: Why is an insert of 1M records slower without a transaction than inside a transaction?
body: <p>I am doing some performance tests using .Net 3.5 against SQL Server. I am doing an insert of 1 million records. When I wrap this inside a transaction (either serializable, RepeatabelRead or ReadUncommited) it runs in under 80 seconds on my system. When I remove the transaction it runs in roughly 300 seconds. I would expect that using no transaction would be the fastest way to insert lines into a database, because the DBMS does not need to take into account a potential rollback. What happens here? Is this typical for SQL Server, the SQL Server ADO.Net Provider, ADO.Net in general, DBMSes in general?</p>
  
  <p>I have a background in iSeries/DB2 databases. In DB2 you have to enable journalling before you can get commitment control and transactions, and journalling is relatively expensive.</p>
  
  <p>What I actually wanted to do was a compare of SqlCommand inserts vs Entity Framework inserts, but I was so surprised at these results that I wanted to find out what is going here first.</p>
  
  <p>Below the code that I use to run the a test. When I run the below code, it takes about 74 seconds (measured between the AtStart log and the AtEnd log lines)</p>
  
  <pre><code>using (SqlConnection sqlConnection = new SqlConnection(connectionString))
  {
      sqlConnection.Open();
      SqlCommand deleteCommand = new SqlCommand("DELETE FROM LockTest");
      deleteCommand.Connection = sqlConnection;
      deleteCommand.ExecuteNonQuery();
  
      using (SqlTransaction transaction = sqlConnection.BeginTransaction(System.Data.IsolationLevel.Serializable))
      {
          try
          {
              if (DEBUG) LOG.Debug("AtStart");
  
              SqlCommand insertCommand = new SqlCommand();
              insertCommand.Connection = sqlConnection;
              insertCommand.Transaction = transaction;
  
              insertCommand.CommandText = "INSERT INTO LockTest (Id, Name, Description, Type) "  + 
                  "VALUES (@id, @name, @description, @type)";
              SqlParameter idParameter = new SqlParameter("@id", System.Data.SqlDbType.UniqueIdentifier);
              insertCommand.Parameters.Add(idParameter);
              SqlParameter nameParameter = new SqlParameter("@name", System.Data.SqlDbType.NVarChar, 50);
              insertCommand.Parameters.Add(nameParameter);
              SqlParameter descriptionParameter = new SqlParameter("@description", System.Data.SqlDbType.NVarChar, Int32.MaxValue);
              insertCommand.Parameters.Add(descriptionParameter);
              SqlParameter typeParameter = new SqlParameter("@type", System.Data.SqlDbType.NChar, 20);
              insertCommand.Parameters.Add(typeParameter);
  
              insertCommand.Prepare();
  
              for (int i= 0; i &lt; 1000000; i++)
              {
                  Guid g = Guid.NewGuid();
                  string s = g.ToString();
                  insertCommand.Parameters["@id"].Value = g;
                  insertCommand.Parameters["@name"].Value = s;
                  insertCommand.Parameters["@description"].Value = DateTime.UtcNow.Ticks.ToString();
                  insertCommand.Parameters["@type"].Value = "test";
                  insertCommand.ExecuteNonQuery();
              }
              transaction.Commit();
          }
          catch
          {
              transaction.Rollback();
              throw;
          }
  
      }
      sqlConnection.Close();
  }
  if (DEBUG) LOG.Debug("AtEnd");
  </code></pre>