Sql Transaction - SQL Server or C#?
c#, sql-server, transactions
Solution
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.
Problem
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?