0

Using SSIS OLE DB Destination, what is the difference between doing fast load with commit size 1 or inserts without the fast load option.

I understand that using fast load is going to invoke the bulk insert command, while the second option is just a simple insert. The actual question is whether there is any performance overhead doing bulk insert with maximum commit size equal to 1.

An explanation of what is happening under the hood in each case will be very appreciated, and whether there is any difference across various versions, starting with 2008.

2
  • Why? Are you trying to solve a problem or are you wanting to learn?
    – Hannah Vernon
    Mar 30, 2016 at 18:20
  • @Max, The problem is doing row by row inserts to clustered columnstore index, and the need to overcome cursors are not supported exception. Reason behind row by row inserts are for error handling, to re-route only "bad" rows to some error table \ file.
    – Ilya
    Mar 31, 2016 at 6:56

1 Answer 1

3

Singleton inserts via a bulk insert technique are significantly slower than normal singleton inserts. To prepare for bulk inserts, the client API must first send column meta data describing the tabular data stream that follows, a task not needed with normal inserts. This overhead is offset by improved performance when streaming multiple rows of data but not when single rows are passed. See https://msdn.microsoft.com/en-us/library/dd304523.aspx for details of the TDS protocol specification.

I ran tests a few years ago for a presentation on insert performance and found single-row bulk insert performance using SqlBulkCopy averaged about 500 rows per second compared to 1,800 per second for inserts. With 100 row batches, bulk inserts averaged over 10K/sec compared to 3.5K with single-threaded singleton inserts. I would expect a similar difference with the IRowsetFastLoad OLE DB interface commonly used in SSIS.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.