Engineering Gratitude πŸŽ‰πŸ‘

Engineering Gratitude πŸŽ‰πŸ‘

SQL Insert Race ! (Performance)

SQL Insert Race ! (Performance)

Single thread vs Parallel thread vs Efcore Bulk extension

Subscribe to my newsletter and never miss my upcoming articles

SQLBulkCopy is a class that can help developers to achieve high-performance operations on DB using c#.

If you have no idea whats the topic is all about then you can check these two articles to get a headstart. Sql Bulk Insert, Sql Bulk Update

On single thread, SqlBulkCopy already excels in terms of data writing but on the internet some folks suggest better performance can be achived by using parallel threads on SQL bulk copy.

My test machine is:

  • Processor: Core I5 6600,
  • Ram: 16gb DDR4,
  • OS: Windows 10 20H2,
  • Dev env:
    • dotnet 5,
    • SQL server 2019

In this test, I am dividing 3 list consists (0.5 million, 2.5 million, 15 million) amount of data.

Our measurement :

  • Single thread bulk copy with all default options.
  • Single thread bulk copy with table lock enabled.
  • Parallel thread bulk copy with table lock enabled.
  • Ef core bulk copy extension. (Although this implements SQLBulkCopy under the hood)

So our data model looks like this

public class Employee
{
    public int Id { get; set; }
    public string EmpId { get; set; }
    public string EmpName { get; set; }
    public bool IsEmployee { get; set; }
    public string Address { get; set; }
    public decimal Salary { get; set; }
    public string AddingBunchOfPropsForTest { get; set; }
    public DateTime CreatedOn { get; set; }
}

Performance result:

I have ran each test 3 times to check these numbers. so here we go...

Data SizeSingle DefaultSingle LockedParallel LockedEF core Bulk Extension
0.5 million2.69 seconds8.06 seconds4.165 seconds10.50 seconds
2.5 million30 seconds20.47 seconds25.16 seconds54.45 seconds
15 million7 mins6 mins5 mins11 mins

To be very frank, I am really surprised to see the performance of efcore bulk extension. Its an open source third party extension, very easy to integrate and its free 😁.

My take:

If you are working with this amount of data (I mean the last category) any kind of raw sql operation should be the first priority. If not then I think orm will just do fine. 😎

If you want to know about what is Default or Table lock ? then I think this official doc can help you to understand this concept very quickly.

Happy learning πŸ’―

Β 
Share this