Inserting Bulk Data in SQL: OLEDB IRowsetFastLoad vs. Ado.Net SqlBulkCopy

Question

I am evaluating different methods for inserting large amount of data in SQL server. I've found SqlBulkCopy class from Ado.Net and IRowsetFastLoad interface from OLEDB. As far as I know, IRowsetFastLoad doesn't map to C#, which is my base platform, so I am evaluating if it would be worth it to create a wrapper around IRowsetFastLoad for .net, so I can use it on my application.

Anyone knows if IRowsetFastLoad would actually perform better than SqlBulkInsert -- Would it be worthy to create such wrapper?

Airspeed

Answer 1

SqlBulkCopy is the managed equivalent of IRowsetFastLoad, they should perform similarly. In the client, as a general rule, OleDB is faster than ADO.Net due to the availability of bindings, which allow for a faster transfer of data in and out the API (less memcopy required because the buffers are known ahead, fixed and pre-allocated). ADO.Net gives a much easier programming model, but is not possible to get data out of ADO.Net w/o a copy. But for all but the most critical access, the difference should be impossible to measure.

Where it comes to the difference that matters, the server access, they both will use the fast load INSERT BULK API (not available from straight T-SQL) and that what really matters.

Drumfish

So, if I actually create a .net wrapper for the IRowsetFastLoad OLE DB; basically I'm introducing the differences that eventually can make my component perform slower than simply using the SqlBulkCopy? – Airspeed

I haven't though about the interop and marshaling to-from native/managed overhead, but you're right, that overhead would further add up. – Drumfish

If you wrapped it using CLI and made sure the call architecture only switched to native in large blocks then I think it may be more performant than SqlBulkCopy. – Maundy