MsSql Bulk Insert With commit size 1 vs Simple Insert

Question

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.

Answer 1

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.