7

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?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Rob
  • 221
  • 5
  • 11

6 Answers6

7

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.

Marc Gravell
  • 1,026,079
  • 266
  • 2,566
  • 2,900
  • I'll be honest and say that I have almost always used transactions in store procs and not very often in c# ado.net. As such the TransactionScope is new to me and I'm glad you mentioned this. Looks like I need to read about this. – Rob Dec 07 '10 at 12:50
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.

Community
  • 1
  • 1
StuartLC
  • 104,537
  • 17
  • 209
  • 285
  • Now that I have read more on TransactionScope and indeed Isolation levels, I'm so glad I raised this question. I wouldn't have understood your comments 5-10 mins earlier :) but do now. Thanks for your reply. – Rob Dec 07 '10 at 13:29
1

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.

Kangkan
  • 15,267
  • 10
  • 70
  • 113
  • Good point about the multiple databases. Most if not all transactions I use are indeed with one database... so perhaps structure all transactions with sql server. Thanks. – Rob Dec 07 '10 at 12:45
  • Yes, in that case you can do it at the server end. – Kangkan Dec 07 '10 at 12:48
1

I belive that the store procedure will have better performance.

Alex Mendez
  • 5,120
  • 1
  • 25
  • 23
1

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...)

Rob
  • 10,004
  • 5
  • 61
  • 91
  • Yes Sql Transactions in C# and using ADO .NET. I forgot about caching. Thanks – Rob Dec 07 '10 at 12:46
  • Having said this I am now not sure whether it wouldn't cache the query plan for the C# query too, there is some trick to getting the EF to cache (I think it's having fixed column lengths or something) so that may apply to ADO.Net queries too - however I believe this only applies in SQL 2008 – Rob Dec 07 '10 at 12:50
  • Query plans are cached and re-used without needing stored procedures – Marc Gravell Dec 07 '10 at 12:52
  • @marc thanks for the info - further to that then why was there so much discussion around EF1 not doing it? and why does EF4 have functionality to turn it on and off? – Rob Dec 07 '10 at 13:00
  • @EF1 - I'd need to see the specific reference; that might mean the internal EF query compilation (which is separate to TSQL place re-use), or it might have been the foobar with things like strings as parameters causing cache miss (i.e. same query with a varchar(11) param and a varchar(5) parameter, because the value in the query was 11 vs 5 chars long, if you see what I mean) – Marc Gravell Dec 07 '10 at 14:46
1

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.

eriksv88
  • 3,482
  • 3
  • 31
  • 50
  • Good point. I read about CLR performs better BUT as oyu say finding errors is a major issue. – Rob Dec 07 '10 at 12:55