none
SqlBulkCopy class vs bcp.exe tool RRS feed

  • Question

  • Hello, I have an app which uses SQLBulkCopy class to read the file and insert rows into SQL Server table. I have compared my app's performance vs the performance of the bcp tool and I would like to ask experts if this could still be improved. I use C# and I have created a class inheriting IDataReader interface. As an example I created a file, comma delimited, with 286000 rows. When using bcp tool I first got slightly over 2 seconds upload and then 1544 ms for each consecutive try. My app, at best, achieved 2171 ms and averages at around 2200-2300 ms for the same file. 

    I wonder if anyone on this board has some experience with this who could confirm if the performance as such is expected or should this be closer - if not the same - to a bcp.exe tool.

    Thank you.

    Wednesday, November 18, 2015 8:27 PM

Answers

  • I wonder if anyone on this board has some experience with this who could confirm if the performance as such is expected or should this be closer - if not the same - to a bcp.exe tool.

    Your observations are about what I would expect.  Keep in mind that BCP is native code and can thus can use pointers and optimization techniques not easily doable in C# plus direct odbc_bcp API calls.   If you need to eek out that sort of performance, unmanaged C or C++ and ODBC is the right tool for the job.  That level of bare metal performance (and related headaches) isn't needed for most LOB applications, though.


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    • Marked as answer by NewLimak Tuesday, November 24, 2015 6:15 AM
    Tuesday, November 24, 2015 2:57 AM

All replies

  • Hello NewLimak,

    >>wonder if anyone on this board has some experience with this who could confirm if the performance as such is expected or should this be closer - if not the same - to a bcp.exe tool.

    In my opinion, they should have a similar same performance because both of them log less data than normal operations and have the ability to instruct SQL Server to ignore its traditional checks and balances on the data coming in.

    If you want to know the best one anyhow, I think the result is it depends on your exact scenario:

    1.If you prepared file meets the requirement to use the bcp.exe(according to this link: Prepare Data for Bulk Export or Import), I think the bcp.exe it would be faster, because if using the SqlBulkCopy, you would have to load it to DataTable firstly.

    2.If the fine does not meet the requirement, you may need to firstly transfer it to the file to meet the requirement, while this should take some time.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, November 19, 2015 9:39 AM
    Moderator
  • Hello Fred, 

    Thank you for response, is it just your opinion or did you test it somehow? 

    N.B. I don't use DataTable, that would be extremely slow compared to what I do. I mentioned in my post I have designed a class built on IDataReader interface. This allows a real time upload of records while parsing the file, row by row, and direct use of SqlBulkCopy.WriteToServer(IDataReader) function. My file is treated like a data source itself.

    If someone has used similar technique then maybe they could share the results. It's not really urgent, I am not a professional developer, but I like coding and I am just curious. I don't need to know the specifics, the only question I would have is whether the similar performance is achievable by using .Net language.

    Thank you.

    Monday, November 23, 2015 10:07 PM
  • >>Thank you for response, is it just your opinion or did you test it somehow?

    Opinion only since I do not have a test environment.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, November 24, 2015 1:50 AM
    Moderator
  • I wonder if anyone on this board has some experience with this who could confirm if the performance as such is expected or should this be closer - if not the same - to a bcp.exe tool.

    Your observations are about what I would expect.  Keep in mind that BCP is native code and can thus can use pointers and optimization techniques not easily doable in C# plus direct odbc_bcp API calls.   If you need to eek out that sort of performance, unmanaged C or C++ and ODBC is the right tool for the job.  That level of bare metal performance (and related headaches) isn't needed for most LOB applications, though.


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    • Marked as answer by NewLimak Tuesday, November 24, 2015 6:15 AM
    Tuesday, November 24, 2015 2:57 AM
  • Thanks, Dan. I will give it a rest. I don't need this sort of performance, it's already exceeded my initial expectations, but when I decided to compare the result with this native tool I became anxious again, I always try to improve solutions.

    Thank you 

    Tuesday, November 24, 2015 6:28 AM