Sql Transaction - SQL Server or C#?

Question

Am I right in saying that from a performance perspective, sql transactions are far better within a stored procedure than code?

At the moment I use most of my transactions in stored procs but sometimes I use code for more complex routines - which obviously I keep to a minimum as much as possible.

It's just that there was a complex routine that required too many "variables" that writing the sql transaction in c# was far easier than using SQL Server. It's a fine line between code readability and performance.

Any ideas?

Answer 1

The performance varies; a SqlTransaction can have less overhead than a TransactionScope, especially if the TransactionScope decides it needs to get entangled with DTC. But I wouldn't expect a vast difference between SqlTransaction and a BEGIN TRAN, except for the extra round trip. However, TransactionScope is still fast, and is the most convenient option for encapsulating multiple operations in a transaction, as the ambient transaction does not need to be manually associated with the command each time.

Perhaps a better (and more significant) factor is the isolation-level. TransactionScope defaults to the highest (serializable). Lower isolation levels allow morefor less blocking (but at the risk of non-repeatable reads, etc). IIRC a TSQL transaction defaults to one of the lower levels. But the isolation level can be tweaked for all 3 options.

Answer 2

I'm for TransactionScope. As per Marc, use a factory method on your TransactionScope to drop the isolation level to READ COMMITTED for most common usages I can think of.

Note that you can use both SQL transactions AND TransactionScope - the SQL BEGIN TRAN / COMMIT TRAN will have little effect on TransactionScope (other than incrementing / decrementing @@TRANCOUNT) - this way if you do need to call the same SQL Sproc elsewhere, e.g. from an adhoc query that you will still get the benefit of a transaction.

The benefit of TransactionScope IMO is that it will manage DTC for you if you DO need to do 2 phase commit (e.g. Multiple databases, Queues or other XA transactions). And with SQL 2005 and later, it works with the Lightweight Transaction Manager, so DTC won't be required e.g. if all accesses are to the one database, one connection at a time.