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?
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.