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();
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();
Using the Execute
method with only one insert statement will never do a bulk insert or be efficient. Even the accepted answer with a Transaction
doesn't do a Bulk Insert
.
If you want to perform a Bulk Insert
, use the SqlBulkCopy
https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy
You will not find anything faster than this.
Disclaimer: I'm the owner of the project Dapper Plus
This project is not free but offers all bulk operations:
(Use under the hood SqlBulkCopy
)
And some more options such as outputting identity values:
// CONFIGURE & MAP entity
DapperPlusManager.Entity<Order>()
.Table("Orders")
.Identity(x => x.ID);
// CHAIN & SAVE entity
connection.BulkInsert(orders)
.AlsoInsert(order => order.Items);
.Include(x => x.ThenMerge(order => order.Invoice)
.AlsoMerge(invoice => invoice.Items))
.AlsoMerge(x => x.ShippingAddress);
Our library supports multiple providers: