6

I analyzed a C# program over the weekend for a customer that basically does the following:

  1. Truncate Table
  2. Fetch Data (source on these is either remote database or local database where job runs)
  3. SQLBulkCopy (.NET class, into destination table of database where job runs)

For the most part (1) and (2) are done through stored procedure calls. I believe SSIS will be more efficient in handling this process. My end result is to determine if SSIS can do a better job and then the most efficient way of designing the package.

Question(s)
In cases where there are a large number of records would SSIS get better performance with the BULK INSERT task when (2) and (3) are the same database on the same instance? Or just change the procedure call in (2) to be a INSERT INTO, taking out the need for (3)?

In cases where (2) is a remote source (another SQL instance on the same network), does SSIS handle pulling data from a remote source more efficiently than doing it through the .NET SQLClient? I would not think there is a huge difference.

As well, when (2) is a remote source that is rather large amount of data (8 million +)would it be better to have a package on the remote instance export the data; then move that file to the destination server and bulk load it?

Improve this question
5
  • Which part of the ETL is the bottleneck? SQLBulkCopy uses BULK INSERT behind the scenes, btw.
    – Jon Seigel
    Jan 22, 2013 at 14:39
  • There are cases when the use of SQLBulkCopy is faster than usual insert (bcp or Bulk Insert). See this article from Linchi Shea on MSDN: Performance Impact: the Most Optimal Insert Script can't Beat BulkCopy
    – Marian
    Jan 22, 2013 at 14:52
  • 1
    You might benefit from reading the following paper: Whitepaper: performance of SqlBulkCopy. It kind of studies the same situation (comparing SSIS with SQLBulkCopy use). @JonSeigel, are you sure that the SQLBulkCopy class is using Bulk Insert behind?
    – Marian
    Jan 22, 2013 at 15:55
  • @Marian: We have an application that uses SqlBulkCopy to load data, and one time I had to run Profiler against it to debug an issue. So yes, in our scenario at least, it uses BULK INSERT. While the documentation of the class doesn't explicitly say it uses BULK INSERT, it does elude to using the same type of logic as bcp. FWIW.
    – Jon Seigel
    Jan 22, 2013 at 18:34
  • @JonSeigel: thanks, that's what I was wondering about. The class doc is not very clear about it.
    – Marian
    Jan 26, 2013 at 0:44

1 Answer 1

Reset to default
4

For 8 million rows, I don't think there will be a lot of difference between SSIS and the SQLBulkCopy class. In SSIS, you do have to make sure you are using the fast load option in the OLE DB Destination, otherwise it will surely be slower. If the source is local, you could try a SQL Server Destination, it might be a tad quicker than the OLE DB Destination.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.