Menu
I found an article by Sam Saffron on bulk inserting with Dapper (That annoying insert problem getting data into the db using dapper) where he ends the article with the statement:
For example, if you need an ultra fast way to insert lots of stuff into a SQL DB, nothing is going to beat SqlBulkCopy and you are going to need a custom API for that.
The article is over 4 years old.
I recently have stumbled across Dapper Plus which claims to be able to do 1,000,000 rows in 2,000ms which would appear to outperform SqlBulkCopy based on many old performance articles I found (such as this one - Evaluating ORMs for batch data).
My Google-fu has unfortunately failed in finding more recent performance comparisons between these two bulk import methods.
Question: Is SqlBulkCopy still faster than Dapper.NET?
998
By default, a bulk copy operation is its own transaction. When you want to perform a dedicated bulk copy operation, create a new instance of SqlBulkCopy with a connection string, or use an existing SqlConnection object without an active transaction.
Dapper provides the Execute method for inserting data. However, a database roundtrip is required for every data you insert. It's a perfect solution when you need to insert only 1 data but become very fast inefficient as soon as you insert multiple data.
The SqlBulkCopy class can be used to write data only to SQL Server tables. But the data source is not limited to SQL Server; any data source can be used, as long as the data can be loaded to a DataTable instance or read with a IDataReader instance.
Upload the data to the temporary table, then perform the SqlBulkCopy update. Using SqlBulkCopy(), upload the datatable's data to the temporary table. Then execute a SQL command to update the main table's data from the temporary table. Finally drop the temporary table.
Disclaimer: I'm the owner of the project Dapper Plus
Dapper Plus for SQL Server/Azure uses SqlBulkCopy under the hood when there are enough entities to save otherwise it will use a SQL derived table.
This article is about Entity Framework, but it's the same strategy for Dapper if you want more information: Entity Framework How to Bulk Insert in SQL Server
So, our library obviously does not outperform SqlBulkCopy, it's the same performance, but our library makes it easier to use.
The library also support:
using SqlBulkCopy and Temporary Table tricks.
139
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
