Dapper bulk inserts are taking longer than planned.

dapper performance sqlbulkcopy

Question

I made the decision to examine more closely at how I was using Dapper after reading this piece.

I ran this code on a blank 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();
}

About 20 seconds passed. That is 2500 inserts per second. Given that the blog was averaging 45k entries per second, not terrible, but also not excellent. Is there a faster method to do this with Dapper?

Aside: It took 3+ minutes long! to run this code via the debugger in Visual Studio. I expected the debugger would slow things down a bit, but I wasn't expecting it to slow it down that much.

UPDATE

then this

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

    scope.Complete();
}

also that

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

each need 20 seconds.

nonetheless, it took 4 seconds!

SqlTransaction trans = connection.BeginTransaction();

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

trans.Commit();
2
65
5/25/2018 3:19:23 PM

Accepted Answer

The best I could get with this method was 50k records in 4 seconds.

SqlTransaction trans = connection.BeginTransaction();

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

trans.Commit();
73
9/26/2012 7:56:45 PM

Expert Answer

With theExecute A technique with just one insert statement is inefficient and incapable of doing bulk inserts. Even the approved response with aTransaction does not aBulk Insert .

If you want to carry out aBulk Insert use theSqlBulkCopy https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy

Nothing moves more quickly than this.

Stylish Plus

I am the project's owner, which is Disclaimer.

This project provides all bulk procedures but is not free:

  • BulkInsert
  • BulkUpdate
  • BulkDelete
  • BulkMerge

(Use the engine's hoodSqlBulkCopy )

Additionally, there are alternatives for producing 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);   

Multiple vendors are supported by our library:

  • Server SQL
  • Compact SQL
  • Oracle
  • MySql
  • PostgreSQL
  • SQLite
  • Firebird
0
5/26/2018 12:10:01 PM


Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow