.net - Why is an insert of 1M records slower without a transaction than inside a transaction? -

I have SQL Server together. Using 3.5 3.5 I'm doing some performance testing. I am making an entry of 1 million records when I wrap it in a transaction (either serial, recyclable or redunamised), it runs under my system under 80 seconds. When I withdraw the transaction, it runs in about 300 seconds I hope that any transaction will be the fastest way to incorporate lines into a database, because DBMS does not need to keep a potential rollback in mind is. What happened here? Is SQL Server, SQL Server ADOnate Provider, Adineat in general, what is typically typical for DBMS?

I have background in the iSeries / DB2 database. You can get commitment control and transactions before enabling journaling in DB2, and journaling is relatively expensive.

What I really wanted to do was comparing the SqlCommand Inserts vs Entity Framework, but I was surprised at these results that I wanted to know what is happening here.

I use the code below to run a test. When I run the code below, it takes about 74 seconds (measured between the extort log and the entry log lines)

 using  (SQL Connection sqlConnection = New SqlConnection (connectionString) } {SqlConnection.Open (); SqlCommand deleteCommand = new SqlCommand ("Remove from locktest"); DeleteCommand.Connection = sqlConnection; DeleteCommand.ExecuteNonQuery (); (Using SqlTransaction Transactions = sqlConnection.BeginTransaction (System.Data.IsolationLevel.Serializable)) {try (DEBUG) LOG.Debug ("AtStart"); SqlCommand insertCommand = new SqlCommand (); InsertCommand.Connection = sqlConnection; InsertCommand.Transaction = Transactions; InsertCommand.CommandText = "Include the locktest (id, name, description, type)" + "value (@id, @name, @description, @type)"; SqlParameter idParameter = New SqlParameter ("@ ID", System.Data.SqlDbType.UniqueIdentifier); InsertCommand.Parameters.Add (idParameter); SqlParameter Name Parameter = New SqlParameter ("@Name", System.Data.SqlDbType.NVarChar, 50); InsertCommand.Parameters.Add (nameParameter); SqlParameter Description Parameter = New SQL parameter ("@ Description", System.Data. SCLDB type.NVarChar, Int32.MaxValue); InsertCommand.Parameters.Add (descriptionParameter); SqlParameter Type Parameter = New SqlParameter ("@ Type", System.Data.SqlDbType.NChar, 20); InsertCommand.Parameters.Add (typeParameter); InsertCommand.Prepare (); For (Int i = 0; I <1000000; i ++) {Guide G = Good. Newguide (); 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 (); } Transactions. Communications (); } Hold {transaction.Rollback (); throw; }} SqlConnection.Close (); } If (DEBUG) LOG.Debug ("AtEnd");  

log flush

each statement with no explicit transaction ( I.e. INSERT) should be done. Unless the data is written to disk on the disk, the committee can not return, which means that every INSERT statement must wait for the write disk operation.

A clear transaction should wait only when the COMMIT statement is issued, and at that time each full log page was already submitted, and the last log page probably has several INSERTs. , So the cost of writing is refined.

Update log

Log flush wait time for total flush time (in milliseconds) to flush the log.

Wait for the log flush to count the number of symbols waiting for the log flush.

log flush / sec log flush number per second