C# – ADO.net SqlTransaction improves performance

ado.netcsql server

I'm doing some work that involves inserting a batch of records into a Sql database. The size of the batch will vary but for arguments sake we can say 5000 records every 5 secs. It is likely to be less though. Multiple processes will be writing to this table, nothing is reading from it.

What I have noticed during a quick test is that using a SqlTransaction around this whole batch insert seems to improve performance.

e.g.

SqlTransaction trans = Connection.BeginTransaction()
myStoredProc.Transaction = trans;
sampleData.ForEach(ExecuteNonQueryAgainstDB);
transaction.Commit();

I'm not interested in having the ability to rollback my changes so I wouldn't have really considered using a transaction except it seems to improve performance. If I remove this Transaction code my inserts go from taking 300ms to around 800ms!

What is the logic for this? Because my understanding is the transaction still writes the data to the DB but locks the records until it is committed. I would have expected this to have an overhead…

What I am looking for is the fastest way to do this insert.

Best Answer

The commit is what costs time. Without your explicit transaction, you have one transaction per query executed. With the explicit transaction, no additional transaction is created for your queries. So, you have one transaction vs. multiple transactions. That's where the performance improvement comes from.

Related Solutions

Sql – How does SQL Server treat statements inside stored procedures with respect to transactions

There will only be one connection, it is what is used to run the procedure, no matter how many SQL commands within the stored procedure.

since you have no explicit BEGIN TRANSACTION in the stored procedure, each statement will run on its own with no ability to rollback any changes if there is any error.

However, if you before you call the stored procedure you issue a BEGIN TRANSACTION, then all statements are grouped within a transaction and can either be COMMITted or ROLLBACKed following stored procedure execution.

From within the stored procedure, you can determine if you are running within a transaction by checking the value of the system variable @@TRANCOUNT (Transact-SQL). A zero means there is no transaction, anything else shows how many nested level of transactions you are in. Depending on your sql server version you could use XACT_STATE (Transact-SQL) too.

If you do the following:

BEGIN TRANSACTION

EXEC my_stored_procedure_with_5_statements_inside @Parma1

COMMIT

everything within the procedure is covered by the transaction, all 6 statements (the EXEC is a statement covered by the transaction, 1+5=6). If you do this:

BEGIN TRANSACTION

EXEC my_stored_procedure_with_5_statements_inside @Parma1
EXEC my_stored_procedure_with_5_statements_inside @Parma1

COMMIT

everything within the two procedure calls are covered by the transaction, all 12 statements (the 2 EXECs are both statement covered by the transaction, 1+5+1+5=12).

C# – SQLTransaction has completed error

You should leave some of the work to compiler, to wrap that in a try/catch/finally for you.

Also, you should expect that Rollback can occasionally throw an exception, if a problem occurs in Commit stage, or if a connection to server breaks. For that reason you should wrap it in a try/catch.

try
{
    transaction.Rollback();
}
catch (Exception ex2)
{
    // This catch block will handle any errors that may have occurred 
    // on the server that would cause the rollback to fail, such as 
    // a closed connection.
    Console.WriteLine("Rollback Exception Type: {0}", ex2.GetType());
    Console.WriteLine("  Message: {0}", ex2.Message);
}

This is copied exactly from MSDN documentation page for Rollback method.

I see that you're worried that you have a zombie transaction. In case you pasted, it doesn't sound like you have a problem. You're transaction has been completed, and you should no longer have anything to do with it. Remove references to it if you hold them, and forget about it.

From MSDN - SqlTransaction.Rollback Method

A Rollback generates an InvalidOperationException if the connection is terminated or if the transaction has already been rolled back on the server.

Rethrow a new exception to tell user that data may not have been saved, and ask her to refresh and review

Best Answer

Related Solutions

Sql – How does SQL Server treat statements inside stored procedures with respect to transactions

C# – SQLTransaction has completed error

Related Topic