Is BULK INSERT faster than SSIS?
Question
Answers
All replies
-
-
When a SSIS developer opted for using the "Fast Load" option along with the "Table lock" on the OLEDB target, or used the SQL Server Destination, then he/she has effectively used the very BULK INSERT, so this is a moot point to debate what is faster.
Bulk insert on its own has tricks, in SQL Server contest more can be done to make it faster a row process, namely making it minimally or not logging at all. Now disabling constraints is another thing the bcp takes care of, not SSIS (unless instructed), and this what MSFT can decide to change in SSIS, but where the SSIS shines is in using an algorithm figuring out what are the best parameters for a given machine/system to use (e.g. the buffer size, etc).
So in most applications the SSIS is faster right away and even more faster with proper tweaking.
In real life many factors bring different impacts to the benchmarking, but at this stage I am inclined to state there is no real measurable difference.
Arthur My Blog
-
Hi,
I'm not surprised bcp/BULK INSERT is faster than SSIS.
Both are launched directly from DB engine, so it has less code and translation layers in the middle.
bcp, BULK INSERT and SSIS are all production strength solutions, otherwise they wouldn't be running on international corporations.
Each one comes with its own pros and cons.
Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu
The fast load option will use BULK INSERT statement instead of INSERT statement, so where is the speed gain coming from?Arthur My Blog
-
Quote from a recent forum thread: "On the processes i use now, im using SSIS (With the max tunning i can), but, even that way, bulk load by TSQL was extremely fast!...we load, every day, about 5~6 files with 10GB+- each, and SSIS is not fast enough (Remember: With max tunning, all options correctly...)..."
http://social.msdn.microsoft.com/Forums/en/transactsql/thread/b322376d-7cbb-4e98-b7f8-c178505d4ede
Is BULK INSERT really faster than SSIS Data Flow? Sometimes? Always?
Kalman Toth SQL SERVER & BI TRAINING
The fastest method of insertion into SQL Server in SSIS uses BULK INSERT under the covers (see Destination Adapter Comparison for details) so its not surprising that a pure BULK INSERT would be quicker because it won't have all the other SSIS fluff running around it.ObjectStorageHelper<T> – A WinRT utility for Windows 8 | http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me
- Proposed as answer by Jamie Thomson Wednesday, March 21, 2012 3:50 PM
-
I am hard pressed to believe that BULK INSERT / bcp is faster than SSIS (any method).
Out of interest, why do you find that hard to believe Kalman?ObjectStorageHelper<T> – A WinRT utility for Windows 8 | http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me
-
And I am curious what "all the other SSIS fluff running around it" is?
Arthur My Blog
-
And I am curious what "all the other SSIS fluff running around it" is?
Logging, a runtime engine, other concurrent tasks etc...
Arthur My Blog
ObjectStorageHelper<T> – A WinRT utility for Windows 8 | http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me
-
And I am curious what "all the other SSIS fluff running around it" is?
Logging, a runtime engine, other concurrent tasks etc...
Arthur My Blog
ObjectStorageHelper<T> – A WinRT utility for Windows 8 | http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me
-
I also don't find it hard to believe that a bcp/BI would be faster than SSIS - but this is a big "if":
- IF your incoming data is text
- IF your incoming text data is formatted exactly as your destination table is defined
- IF your incoming data requires NO semi-complex data conversions (only implicit)
- IF your incoming data has no fatally bad rows (alphas in numeric columns, etc)
- IF your incoming data has no ambiguities, interpretations, or lookups to do
Talk to me now on
-
With flat file sources you can indicate some columns as fast-parse (mostly integer columns) in SSIS, which makes process them even faster.
MCTS, MCITP - Please mark posts as answered where appropriate.
-
You can certainly make BULK INSERT do minimal logging but you cannot make it do no logging. If you think you have a way to do that, please post the code that does it.When a SSIS developer opted for using the "Fast Load" option along with the "Table lock" on the OLEDB target, or used the SQL Server Destination, then he/she has effectively used the very BULK INSERT, so this is a moot point to debate what is faster.
Bulk insert on its own has tricks, in SQL Server contest more can be done to make it faster a row process, namely making it minimally or not logging at all. Now disabling constraints is another thing the bcp takes care of, not SSIS (unless instructed), and this what MSFT can decide to change in SSIS, but where the SSIS shines is in using an algorithm figuring out what are the best parameters for a given machine/system to use (e.g. the buffer size, etc).
So in most applications the SSIS is faster right away and even more faster with proper tweaking.
In real life many factors bring different impacts to the benchmarking, but at this stage I am inclined to state there is no real measurable difference.
Arthur My Blog
--Jeff Moden
