Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
Which part of the ETL is the bottleneck? SQLBulkCopy uses BULK INSERT behind the scenes, btw. –  Jon Seigel Jan 22 '13 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 '13 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 '13 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 '13 at 18:34
    
@JonSeigel: thanks, that's what I was wondering about. The class doc is not very clear about it. –  Marian Jan 26 '13 at 0:44
add comment

1 Answer 1

up vote 2 down vote accepted

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.