During a conversation about the lovely library called Dapper and CRUD I was made aware of the existence of an object called TransactionScope, which MSDN describes as:

Makes a code block transactional.

Big words hinting at magic.

I immediately started wondering what the difference was, compared to SqlTransaction, which is the object that most people are accustomed to working with. As the conversation continued someone mentioned that TransactionScope had a huge performance hit compared to SqlTransaction during bulk operations, or so they’d read on Stack Overflow (http://goo.gl/HSEf65).

Apparently the poster was claiming that 50 000 inserts where taking around 20 seconds to finish using TransactionScope and only around 4 seconds when using SqlTransaction.

In some cases that’s the end and most people will ignore TransactionScope and just use SqlTransaction instead, I however chose to do my own tests. I created a database with a table both localy and on one of our servers, wrote my own tests and timed it. The results didn’t match up with the ones posted by the poster.

The poster claimed the following:

• TransactionScope: 2 500 inserts/sec
• SqlTransaction: 12 500 inserts/sec

My tests gave me these results:

• TransactionScope (local db): 10 200 inserts/sec
• SqlTransaction (local db): 10 250 inserts/sec
• TransactionScope (server db): 740 inserts/sec
• SqlTransaction (server db): 760 inserts/sec

TransactionScope gave me the same performance as SqlTransaction, in some cases it was faster.

I’m not saying the poster lied, he probably got those results at the time; in fact I’m sure of it. How can I be sure of it? If you know how to use TransactionScope you’ll have noted that he opened his connection before he created the TransactionScope, which means that the connection was never associated with the TransactionScope (Execute does not open the connection).

What I’m saying is this: Write a snippet and test stuff for yourself, don’t take other people’s test results at face value.