I am doing some performance tests using .Net 3.5 for SQL Server. I am doing an insertion of 1 million records. When I transfer this inside a transaction (either serializable, RepeatabelRead or ReadUncommited), it runs for 80 seconds on my system. When I delete a transaction, it starts in about 300 seconds. I would expect that using a transaction would not be the fastest way to insert rows into a database, because there is no need to consider potential rollback in the DBMS. What's going on here? Is this typical of SQL Server, the ADO.Net SQL Server provider, ADO.Net in general, the DBMS in general?
I have a background in iSeries / DB2 databases. In DB2, you must enable logging before you can take control of transactions and transactions, and publishing logs is relatively expensive.
What I really wanted to do was compare the SqlCommand vs Entity Framework inserts, but I was so surprised by these results that I wanted to first know what was going on here.
Below is the code I use to run the test. When I run the code below, it takes about 74 seconds (measured between the AtStart log and the AtEnd log lines)
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 < 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");
source
share