Bulk inserts taking longer than expected using Dapper

Question

After reading this article I decided to take a closer look at the way I was using Dapper.

I ran this code on an empty database

var members = new List<Member>(); for (int i = 0; i < 50000; i++) {     members.Add(new Member()     {         Username = i.toString(),         IsActive = true     }); }  using (var scope = new TransactionScope()) {     connection.Execute(@" insert Member(Username, IsActive) values(@Username, @IsActive)", members);      scope.Complete(); } 

it took about 20 seconds. That's 2500 inserts/second. Not bad, but not great either considering the blog was achieving 45k inserts/second. Is there a more efficient way to do this in Dapper?

Also, as a side note, running this code through the Visual Studio debugger took over 3 minutes! I figured the debugger would slow it down a little, but I was really surprised to see that much.

UPDATE

So this

using (var scope = new TransactionScope()) {     connection.Execute(@" insert Member(Username, IsActive) values(@Username, @IsActive)", members);      scope.Complete(); } 

and this

    connection.Execute(@" insert Member(Username, IsActive) values(@Username, @IsActive)", members); 

both took 20 seconds.

But this took 4 seconds!

SqlTransaction trans = connection.BeginTransaction();  connection.Execute(@" insert Member(Username, IsActive) values(@Username, @IsActive)", members, transaction: trans);  trans.Commit(); 

Answer 1

The best I was able to achieve was 50k records in 4 seconds using this approach

SqlTransaction trans = connection.BeginTransaction();  connection.Execute(@" insert Member(Username, IsActive) values(@Username, @IsActive)", members, transaction: trans);  trans.Commit(); 

Answer 2

I stumbled accross this recently and noticed that the TransactionScope is created after the connection is opened (I assume this since Dappers Execute doesn't open the connection, unlike Query). According to the answer Q4 here: https://stackoverflow.com/a/2886326/455904 that will not result in the connection to be handled by the TransactionScope. My workmate did some quick tests, and opening the connection outside the TransactionScope drastically decreased performance.

So changing to the following should work:

// Assuming the connection isn't already open using (var scope = new TransactionScope()) {     connection.Open();     connection.Execute(@" insert Member(Username, IsActive) values(@Username, @IsActive)", members);      scope.Complete(); }