locked
Is BULK INSERT faster than SSIS? RRS feed

Answers

All replies

  • Hello Kalman,

    what would you like us to do with your post?


    Arthur My Blog

    Wednesday, March 21, 2012 3:04 PM
  • Arthur,

    I am hard pressed to believe that BULK INSERT / bcp is faster than SSIS (any method).  Yes, no, or maybe?

    Even if BULK INSERT faster, is it production strength? I believe not.


    Kalman Toth SQL SERVER & BI TRAINING


    • Edited by Kalman Toth Wednesday, March 21, 2012 3:09 PM
    Wednesday, March 21, 2012 3:08 PM
  • 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

    Wednesday, March 21, 2012 3:17 PM
  • 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

    Wednesday, March 21, 2012 3:20 PM
  • 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

    Wednesday, March 21, 2012 3:33 PM
  • 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
    Jamie Thomson

    • Proposed as answer by Jamie Thomson Wednesday, March 21, 2012 3:50 PM
    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
    Jamie Thomson

    Wednesday, March 21, 2012 3:52 PM
  • And I am curious what "all the other SSIS fluff running around it" is?

    Arthur My Blog

    Wednesday, March 21, 2012 5:46 PM
  • And I am curious what "all the other SSIS fluff running around it" is?

    Arthur My Blog

    Logging, a runtime engine, other concurrent tasks etc...

    ObjectStorageHelper<T> – A WinRT utility for Windows 8 | http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me
    Jamie Thomson

    Wednesday, March 21, 2012 6:16 PM
  • And I am curious what "all the other SSIS fluff running around it" is?

    Arthur My Blog

    Logging, a runtime engine, other concurrent tasks etc...

    ObjectStorageHelper<T> – A WinRT utility for Windows 8 | http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me
    Jamie Thomson

    Wednesday, March 21, 2012 6:16 PM
  • bcp, BULK INSERT and SSIS are all production strength solutions,

    What I was referring to that SSIS has optional extensive error logging and sophisticated error control.

    T-SQL forum on a typical day has 5 or so my bcp not working (worked yesterday), my BULK INSERT not working and my linked server broken. For a regular database developer they appear pretty much like black boxes with some esoteric error messages such "did not find end of file marker" and so on.


    Kalman Toth SQL SERVER & BI TRAINING


    • Edited by Kalman Toth Wednesday, March 21, 2012 7:39 PM
    Wednesday, March 21, 2012 7:39 PM
  • Hello,

    Very interesting thread. Enjoyed reading it full of information.So the final conclusion is that Bulk Insert is faster right?

    Thursday, March 22, 2012 5:24 AM
  • 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

    Todd McDermid's Blog Talk to me now on

    Thursday, March 22, 2012 5:54 AM
  • 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.

    Thursday, March 22, 2012 6:07 AM
  • IF your incoming data has no fatally bad rows (alphas in numeric columns, etc)

    ... and missing end of row / file markers, hidden characters.  These are pretty nasty errors with bcp / Bulk Insert. My favorite bcp error message is "unexpected end of file". Guaranteed to turn a database developer's productive day into ruins.


    Kalman Toth SQL SERVER & BI TRAINING


    • Edited by Kalman Toth Thursday, March 22, 2012 5:44 PM
    Thursday, March 22, 2012 5:30 PM
  • Hi, just adding an interesting article that compares the different load strategies for achieving high performance:

    The Data Loading Performance Guide

    David.

    • Edited by dac03 Thursday, March 22, 2012 6:30 PM
    • Proposed as answer by Eileen Zhao Monday, March 26, 2012 6:28 AM
    • Marked as answer by Eileen Zhao Thursday, March 29, 2012 1:52 AM
    Thursday, March 22, 2012 5:59 PM
  • 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

    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.

    --Jeff Moden

    Saturday, March 28, 2020 6:45 AM