Bulk insert via Dapper is slower than inserting rows one-by-one

I'm not sure why you are using Dapper Execute extension method if you want the best performance available.

The best free way to insert with the best performance is using the SqlBulkCopy class directly.

Disclaimer: I'm the owner of the project Dapper Plus

This project provides easy support for the following operations:

• BulkInsert
• BulkUpdate
• BulkDelete
• BulkMerge

Example:

// 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);   

The ideal batch size can vary from Server to Server, the key factor is Logging, this will affect how resource intensive the Insert is and how large you can make your batches before you see a performance drop-off.

The key to fast Inserts/Updates is ensuring you meet the requirements for Minimal Logging, please look at this Microsoft White Paper. Don't panic - you don't need to read all of it - look at the table describing the conditions to meet 'minimal logging' (you will need to talk to your DBA).

Once you've got as little Logging as possible, then you need to look at your execution plans for SPOOLS, if a batch starts to spool to Tempdb then you will hit a dramatic slow down. The key is to keep the batch small enough to remain in the buffer (RAM). But the amount of buffer space available will depend on other processes.

Note: TABLOCK is not the same as TABLOCKX.