What Is the Fastest Way To Do a Bulk Insert? Let’s Find Out

Hey Milan, any discount incoming for the The Ultimate Modular Monolith Blueprint course? I bought your Clean Architecture course and I loved it. Just wondering if there is a sale soon or discount for the Modular Monolith Course. Thanks

All shown methods are slow compared to „load data from file“. Whenever possible for large data imports use load data from file. It will load gigabyte of data within seconds. One of the best approaches in my experience is to create a temporary table for the datasource and do the load data file command. Then perform the inserts to the entity tables on the database server.
Only issue / drawback can be the network connection when loading large datasets.

It also looks like there is a library called dapper plus that has bulk insert feature as well. Also a commercial paid library.

A few things.

You never adjusted the batch size for EF Core. It is possible to speed up inserts by increasing the batch since. I think by default it is 100.
Also bulk-copy has a way to set the batch size. By default I believe it is set to 0 which means all rows. But, its recommended to use it.

Bulk-copy by default does a non-trasacted insert. So, if there is an issue there is no way to roll it back. There is an option to have it use a transaction, but I assume that will slow it down a bit.

I'm curious if you match the bulkcopy and efcore batch size settings and enable Internal transactions in bulk-copy if the speeds would be closer?

I'm not sure, but did your code create the collection each time? Perhaps to remove the overhead of that you could create the user collection in the constructor?

What do you think about Bulk Update? Can you run Benchmark for us?

Imho, it's a shame, but really a marketing issue that we have to do so a lot of work to perform bulk inserts in sql databases, unless you use a library.

NoSQL databases such as MongoDb have bulk operations out of the box and completely free and easy to use

Surprisingly Dapper doesn't perform well. Still I would like to see results when using Dapper with SQL Bulk Insert command.

I personally have used a EFCore.Extensions library, which is a paid one, to do the bulk inserts. My company bought a license for this library and it saved many development days for such things as bulk merge and bulk synchronize operations.
Interesting to compare its performance to sql bulk copy class

So DataTable is EF core without paid lib?

It's possible to check for dapper with executing SP that accept UDT table as parameter?

Thank you, Milan! This video must be in top.

BulkExtensions just for lazy programming) BulkCopy rulez!)

Thanks for doing the research !
Very insightful investigation

Love this. One quick qn: For the EFCore approaches, would the performance be consistent on Postgres as well as SQL server?

As my mentor always said, you can never beat microsoft in optimizing bulk inserts.

I also want to put out there that using that if you have the table ready in the database, then using an IDataReader with SqlBulkCopy will yield even more speed. We're talking milliseconds for a million++ rows.

Another best tutorial!
Thanks!

Have you try OpenJson or other json structure with raw sql query?

SQLBulkcopy. It will suspend constraints on the table during insert.

My repository creates a datatable, inserts 25K records from a passed collection, then it sends that datatable to a sproc.
I use this same format for all Sel, Del, Upsert.

Dynamic SQL is slower than sprocs.

How dapper can be 5 times slower at 1m records than efcore addall? This doesn't make sense at all.

Just a suggerstion. Prepare seriaes for GrapgQL with hotchocolate

Another alternative, if you already have files ready to import, is to use inside of SQL Server the OPENROWSET command:
INSERT INTO Table (
Col1, Col2, ...
)
SELECT
Col1, Col2, ..
FROM OPENROWSET(
BULK 'c:\myfile.txt', FORMATFILE='c:\format.xml'
)
In the XML file, you define the rules in how the file you want to import is formatted (fixed size, comma split, etc...)

Milan just wanted to say, thanks to you and your C# videos, i managed to land a job. Appreciate ya pal ;)

I like using the BulkCopy function, it is amazingly fast for importing large datasets. One thing to note is that the column names specified for the database side are case sensitive. If there's a mismatch in case on column names, the import will fail. You can also eek out even more performance by tweaking the batch size in BulkCopy using `bulk.BatchSize = 10_000;`. Actual performance will vary based on how many columns you're inserting.

Can you try to concatenate the insert query then try sql raw query with ef and see the results

Thanks for the video! I did not know the EF bulk extension - it seems very practical.
I deal with large datasets only - that's why I use SqlBulkCopy for almost all insert operations. ( + some wrappers to allow dynamic DataTable building before passing to SqlBullkCopy).

Thank you for the video. I'd like to see a video on bulk upserts/merging data

😊