none
Row-by-row processing and BulkCopy.WriteToServer RRS feed

  • Question

  • Hi All!
     
      I have old extended stored procedure DLL, written in C++. This procedure opens select over source table, creates IRowsetFastLoad interface for fast results saving and does some complex data processing. Now it is time to move this processing to NET (we a going to use 64-bit server). And - tada! - mission is impossible. There is no suitable WriteToServer overload. You can't just pool row-by row in cycle. You must use DataTable or DataReader. DataReader is inappropriate in this scenario. DataTable is inappropriate too, but it is only way. So I fill DataTable by 10000 rows, write it back to server, clean and so on. It works 6-15 times slower than C++ code and consumes lot of memory.
      Is there any workaround over this situation? I feel upset at this point. C# code much shorter and simpler than C++, it works faster at data-processing part. But looks like I have to maintain old C++ code Sad
     
    Thank you,
    Alexander
    Monday, June 9, 2008 1:54 PM

All replies

  • Unfortunately input tables have different structures and it is hard to create package to handle this task. I think it could be implemented as SSIS package but I'm not sure it is faster and requires less memory than NET SP. One more issue - application code should be changed to innitiate SSIS package.
     
    Thank you,
    Alexander
    Monday, June 9, 2008 3:32 PM
  • If you have the data file on the sql server machine you may try BULK INSERT TSQL statement as well (and do transformation in TSQL later). Depending on how complicated the transformation is, TSQL might or might not meet your need.

     

    If you need to use ADO.NET you may look at the System.Data.SqlClient.SqlBulkCopy class or so.

     

    However I agree it's generally not recommended to run large data transfer/load in extended stored procedures or sqlclr. SSIS is the way to go.

     

    You may create a Sqlagent job to execute the package and still run a stored procedure to initiate the job execution.

    Thursday, June 12, 2008 7:32 AM
  • Thank you for answering but I wonder, have you read my original message? As I wrote data processing is complex, and TSQL is not enough. I do use SqlBulkCopy for this task. It is not a huge data transfer\load, it is complicated one. SSIS is a perfect tool, but it is too heavy for this task.
      My general idea - new
    SqlBulkCopy class implementation doesn't work well in this scenario and there is no workaround. So old C++ code beats C# implementation. There is some space for improvement. New WriteToServer overload which takes object[] and may be some extra methods could resolve this situation. This functionality works well in old ADO but id is lost in new one.

    Thank you,
    Alexander
    Thursday, June 12, 2008 1:20 PM