I had someone ask an ADO.NET question last night that I just couldn’t answer. It was a little more about distributed transactions which I have little experience with. So I had him type the question into Notepad on my laptop. Twenty minutes later, I saw Pablo Castro and had him type the answer so that I could get it back to Ziga without misinterpretation. Pablo also filled me in so that I understood the question as well as the answer. I thought I would share it here….

Q. What’s the performance difference when using a transaction scope vs. assigning transactions to connections & sqlcommands the ado.net 1.1 way?

A. Short answer (there is much more to this topic)

If you’re using System.Transactions against a SQL Server 2005 server, then the cost is more or less the same (assuming that you don’t bring more than one Sql connection to the scope).

On the other hand, if you’re hitting a SQL Server 2000 server, then System.Transactions will be more expensive because we’ll “promote” the transaction (into a distributed transaction) even for the very first connection.

Posted from BLInk!

  Sign up for my newsletter so you don't miss my conference & Pluralsight course announcements!