In Search of a FAST SQL 2012 Bulk Insert in VB.NET

Seekers,

My INSERTs of 50,000 rows at a time into SQL Server 2012 were slow-really slow. I’m using EF6, so I thought it would be easy to find an example of passing a batch of entities to a Stored Procedure, and viola! I was shocked to find there is no such thing.

I’m not on a mission to teach the world, or do research… I just need to get my rows in faster, as the inserts were taking so long I was getting these errors:

System.Data.Entity.Infrastructure.DbUpdateException: An error occurred while updating the entries. See the inner exception for details. —> System.Data.Entity.Core.UpdateException: An error occurred while updating the entries. See the inner exception for details. —> System.Data.SqlClient.SqlException: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding. —> System.ComponentModel.Win32Exception: The wait operation timed out

I found some code on a Japanese site that helped me out, and I can’t figure out how to thank the author. I can’t even tell if the snippet was lifted fro somewhere else… I converted it to VB.NET, and made an overload to it to make it easier to use. Here are the 2

Notes:

1 log.XXXX is for an implementation of Kellerman Netlogger…

2 I kept as much of the code exactly as I found it, so whoever I can credit can find it easier.

3. I was too lazy to change them to Functions, returning a String, having the string be a summary of performance, like I do below when calling the Sub():

We’ve shown more than a few times that DB beats Code every time, so let’s see how this innocent little Sub does:

[2014-05-11 20:05:06,382] Saved 1079 lines of data for A in 14.31 seconds (75.4239080521187 rows/sec)
[2014-05-11 20:05:22,166] Saved 2477 lines of data for A in 0.09 seconds (26566.3359370559 rows/sec)
[2014-05-11 20:05:37,263] Saved 423 lines of data for A in 0.02 seconds (17422.8944246738 rows/sec)
[2014-05-11 20:05:38,993] Saved 5681 lines of data for B in 0.14 seconds (41218.2492147782 rows/sec)
[2014-05-11 20:05:43,150] Saved 23387 lines of data for B in 0.52 seconds (44982.6730059531 rows/sec)
[2014-05-11 20:05:44,580] Saved 3435 lines of data for B in 0.07 seconds (47229.9984050423 rows/sec)
[2014-05-11 20:05:47,539] Saved 23213 lines of data for C in 0.35 seconds (65858.9467221463 rows/sec)
[2014-05-11 20:05:56,103] Saved 54592 lines of data for C in 1.28 seconds (42764.6493521225 rows/sec)
[2014-05-11 20:06:00,823] Saved 31691 lines of data for C in 0.71 seconds (44690.0100320195 rows/sec)
[2014-05-11 20:06:02,280] Saved 4266 lines of data for D in 0.07 seconds (59780.2173163643 rows/sec)
[2014-05-11 20:06:04,526] Saved 8909 lines of data for D in 0.20 seconds (43829.2464901919 rows/sec)
[2014-05-11 20:06:06,832] Saved 11589 lines of data for D in 0.24 seconds (48576.0620485945 rows/sec)
[2014-05-11 20:06:10,297] Saved 31256 lines of data for E in 0.48 seconds (64928.7655246037 rows/sec)
[2014-05-11 20:06:18,315] Saved 51726 lines of data for E in 1.17 seconds (44346.0272686145 rows/sec)
[2014-05-11 20:06:24,070] Saved 42475 lines of data for E in 0.86 seconds (49152.4998110855 rows/sec)

…a little slow to get started, but man does she fly!

rowSpeed

 

 

 

 

 

 

Good enough for me! No more silly SQL Server timeouts!

pat
:)

Leave a Reply

Your email address will not be published. Required fields are marked *