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

ado.net oledb sqlbulkcopy sql-server

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?

Accepted Answer

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.



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why