33145

SqlBulkCopy on a single record?

Question:

My software can be deployed in two different configurations: one where I receive a trickle-feed of single data points sporadically throughout the day, and another where I get a dump of all data at the end of the day. For the end-of-day dump, obviously, I will use a SqlBulkCopy() command with a configurable batch size.

However, for simplicity, I'd like to just use the same code in the trickle-feed by setting the batch size to 1. Is there significant overhead by doing this? Would I be better off doing single INSERT calls for a trickle-feed?

The workflow looks like this:

ICollection<MyClass> dataPoints = ...; public void AddDataPoint(MyClass data) { dataPoints.Add(data); if (dataPoints.Count >= ConfigurableBatchSize) { DoBulkCopy(dataPoints); // converts MyClass objects into rows of a DataTable, etc } }

Answer1:

Would you take a train to the corner grocery store? You could use bulk copy for an occasional single-row insert but I recommend against that if the single row is the rule rather than the exception, as appears to be your use case. That said, you might not notice the overhead at all if the inserts are few. The main thing you want to avoid is a continuous stream of single-row batches.

I ran many performance tests of various insert methods and sizes for at a SQL Saturday session on Maximizing SQL Server Insert Performance a couple of years ago. With a singe-thread app, single-row parameterized inserts achieved a rate of about 2,200 per second whereas singe-row SqlBulkCopy with a DataTable source was about 500/sec (with double the server CPU time). You can download the Power Point from <a href="http://www.sqlsaturday.com/154/schedule.aspx" rel="nofollow">sqlsaturday.com/154/schedule.aspx</a>.

Recommend

  • add geom_line to link all the geom_point in boxplot conditioned on a factor with ggplot2
  • JUnit: Run one test with different configurations
  • google maps API geocode finds nearby KML
  • Bubble chart , scatter chart ,candlestick chart using core plot
  • EF Code First Foreign Key's
  • Java .class.getResourceAsStream()
  • can i initialize an array outside the method just like we initialize a member/field variable
  • Run python unit tests as an option of the program
  • Foreign key pointing to Compound Key
  • How to declare a typescript property that implements multiple interfaces
  • Issue loading child entity of a parent entity. Unidirectional mapping and 1 to 0..1 relationship wit
  • Finding a value of range less than or greater than from container class
  • Restrict execution of a Method with Java Annotations
  • Android Notification FLAG_AUTO_CANCEL not working
  • How can I use my enum in QString.arg()?
  • Declare a C++ class without defining it in the current translation unit
  • Simple app using Windows Workflow and winforms NOT console
  • Can't use openlayers-3 with webpack
  • Using constants or global variables in 3 tier console application
  • Checking a play current mode makes an error occur
  • Mercurial: Identify file name after rename
  • Creating Java object from class name with constructor, which contains parameters [duplicate]
  • Does CUDA 5 support STL or THRUST inside the device code?
  • PHP: When would you need the self:: keyword?
  • Suggestions to manage Login/Logout transitions
  • JTable with a ScrollPane misbehaving
  • Angular 2 constructor injection vs direct access
  • Java static initializers and reflection
  • Exception on Android 4.0 `android.os.StrictMode$AndroidBlockGuardPolicy.onNetwork(StrictMode)`
  • Android Google Maps API OnLocationChanged only called once
  • How to get Windows thread pool to call class member function?
  • unknown Exception android
  • EntityFramework adding new object to nested object collection
  • Checking variable from a different class in C#
  • Observable and ngFor in Angular 2
  • How to Embed XSL into XML
  • failed to connect to specific WiFi in android programmatically
  • UserPrincipal.Current returns apppool on IIS
  • Conditional In-Line CSS for IE and Others?
  • How can I use threading to 'tick' a timer to be accessed by other threads?