1

Possibly a duplicate of this. I'm trying to retrieve data from BigQuery and trying to insert it into SQL Server. I am able to retrieve data from BigQuery relatively easily. But the problem is, despite the following code (inspired from the duplicate question), even with 100K rows its taking 97+ seconds to insert that data into SQL Server!

using( var conn = new SqlConnection( dbConnString ) )
{
     conn.Open();

     try
     {
         using( var sqlBulkCopy = new SqlBulkCopy( conn, SqlBulkCopyOptions.TableLock | SqlBulkCopyOptions.FireTriggers | SqlBulkCopyOptions.UseInternalTransaction, null ) )
         {
             sqlBulkCopy.BulkCopyTimeout = 100;
             sqlBulkCopy.DestinationTableName = table.TableName;
             sw.Start(); //StopWatch
             sqlBulkCopy.WriteToServer( table );
         }

         sw.Stop();

         Console.WriteLine( $"Time taken to INSERT data: {sw.ElapsedMilliseconds}" );
     }
     catch( Exception ex )
     {
         Console.WriteLine( $"Error while inserting in to DB: {ex.Message}" );
     }
}
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user13309289
  • 217
  • 2
  • 8
  • Inserting data takes more time rather then retrieving it as usually – west Jul 17 '20 at 17:30
  • 1
    Are there any indexes? Or triggers on the destination table? – granadaCoder Jul 17 '20 at 17:32
  • 3
    Yep, triggers are a likely culprit since you explicitly have that enabled. One possiblity not yet mentioned is the database autogrowth settings, which is a ridiculously low 1 MB by default for the data file, and 10% for the log. Checking that [instant file initialization](https://learn.microsoft.com/en-us/sql/relational-databases/databases/database-instant-file-initialization?view=sql-server-ver15) is on would also be a good idea. – allmhuran Jul 17 '20 at 17:38
  • @granadaCoder and allmhuran - No triggers, no indexes on the destination table. – user13309289 Jul 17 '20 at 19:06
  • @user13309289 did you check the database size allocation, autogrowth, and instant file initialization settings? – allmhuran Jul 18 '20 at 13:34

1 Answers1

2

By default, SqlBulkCopy will process the operation in a single batch. If you have 100K rows to copy, 100K rows will be copied at once.

You should specify the BatchSize adding the following row in your code:

bulkCopy.BatchSize = 5000;

Not specifying a BatchSize can impact your application:

  • Decrease SqlBulkCopy performance
  • Increase the chance to get a Timeout Expired exception
  • Increase the chance to get an OutOfMemory exception
  • Impact server performance
  • Impact database server performance

Batch size of 5,000 to be the best compromise of speed and memory consumption.

For more detail look the following question:

What is the recommended batch size for SqlBulkCopy?

Here, for completeness I added the best response (In my opinion)

I have an import utility sitting on the same physical server as my SQL Server instance. Using a custom IDataReader, it parses flat files and inserts them into a database using SQLBulkCopy. A typical file has about 6M qualified rows, averaging 5 columns of decimal and short text, about 30 bytes per row. Given this scenario, I found a batch size of 5,000 to be the best compromise of speed and memory consumption. I started with 500 and experimented with larger. I found 5000 to be 2.5x faster, on average, than 500. Inserting the 6 million rows takes about 30 seconds with a batch size of 5,000 and about 80 seconds with batch size of 500. 10,000 was not measurably faster. Moving up to 50,000 improved the speed by a few percentage points but it's not worth the increased load on the server. Above 50,000 showed no improvements in speed. This isn't a formula, but it's another data point for you to use.

Gabriele Franco
  • 879
  • 6
  • 10
  • Fra After putting those things in place `sqlBulkCopy.EnableStreaming = true; sqlBulkCopy.BatchSize = 5000; sqlBulkCopy.NotifyAfter = 20000; sqlBulkCopy.SqlRowsCopied += ( sender, e ) => Console.WriteLine( "RowsCopied: " + e.RowsCopied ); sqlBulkCopy.BulkCopyTimeout = 100;` still no luck, time taken still around 2 minutes. – user13309289 Jul 20 '20 at 12:17
  • Ok, now you should check: 1- Duration of every single batch with Extended Events or SQL Profiler 2- Check network speed Let me know – Gabriele Franco Jul 20 '20 at 13:47
  • Don't have access to Extended Events or SQL Profiler. But `SqlRowsCopied` event reports ~5 secs to insert 5K records. Network speed 10.54 Mbps down, 0.72 up. – user13309289 Jul 20 '20 at 17:40
  • Can you add the execution plan in xml format? 5 seconds for 5000 records is really a long time – Gabriele Franco Jul 20 '20 at 18:55
  • Dont believe there is one for `SqlBulkCopy` class. – user13309289 Jul 20 '20 at 19:12