Sql Transaction - SQL Server or C#?

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 the开发者_运维知识库re 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?

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.

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.

Transaction management in the code (read c#) is an option and to be followed for managing transactions over multiple data source or system. For managing transaction for a single database, the management at the server end will be always simpler. But if you think that the code might need to cater to a scenario where multiple data-sources get added in the transaction, keep the transactions in the code level.

I belive that the store procedure will have better performance.

If you mean you write SQL Transactions in C# and use ADO.Net or similar to execute them then they are probably less efficient because SQL will cache the query plan for a stored procedure (which is also now the case for Entity Framework - though still not as quick as proc I don't think) so really you should probably be doing it the other way round - complex procedures in SQL to get the caching benefits (if only it were that simple...)

It depends on what application.

But I will say that in most cases it is best to surprised to have logic in the database. A very advantageous also to have business logic in the databse, is that then it will be the same even if you have riders a WinForms version and a web, or whatever

But if you're talking about the CLR in SQL. The negative with this, it becomes much more difficult for a DBA to find any errors or something about. performance.