locked
Compare Performance (Execute SQL Task Insert and Data Flow Task) RRS feed

  • Question

  •  

    I am using SQL 2005 SSIS. I am joining several large tables and then the move result into another table in the same database.

     

    I would like know which method is faster:

    1. Use Execute SQL Task to insert the result set to the target table
    2. Use the Data Flow Task to insert the result set to the target table. (Use OLE DB source to execute SQL command and then use the SQL destination)

    Could you tell me why then other is slower?

     

    Thanks.

    Wednesday, March 12, 2008 2:48 PM

Answers

  • 1) Do both, compare the results and you let us know which is faster. The performance is going to depend on tons of variables unique to your environment, so that is the only way to know for sure.

     

    2) In general, if both tables are in the same database, an INSERT..SELECT is going to be much faster than any other technique, because it can take advantage of locality of data, and no data needs to move between processes or across the network.
    Wednesday, March 12, 2008 3:01 PM
  • Depending on how large is the data set being inserted, executing the load in one single batch in a execute sql task could be a problem, as all rows will be committed in one transaction. In the data flow you have control over the size of each batch.

    Wednesday, March 12, 2008 3:08 PM

All replies

  •  

    Depending on what you're doing, the execute SQL task will be faster because it will use the DB engine for the join and insert; there will be no data moved across the network.  Using a data flow requires the data to stream across the network.  If, in that data flow, you perform your join in a single OLE DB source, I would expect performance to be reasonably close to using the execute sql task, but the end result is that the data will have to be sent to the machine running SSIS and then back via the OLE DB Destination component.

     

    But, as we usually say around here, try it yourself and report back your findings.

     

    Wednesday, March 12, 2008 2:57 PM
  • 1) Do both, compare the results and you let us know which is faster. The performance is going to depend on tons of variables unique to your environment, so that is the only way to know for sure.

     

    2) In general, if both tables are in the same database, an INSERT..SELECT is going to be much faster than any other technique, because it can take advantage of locality of data, and no data needs to move between processes or across the network.
    Wednesday, March 12, 2008 3:01 PM
  •  Phil Brammer wrote:

     

    But, as we usually say around here, try it yourself and report back your findings.

     

     

    Looks like my forum-fu is slower than your forum-fu this morning.
    Wednesday, March 12, 2008 3:05 PM
  •  MatthewRoche wrote:

     

    Looks like my forum-fu is slower than your forum-fu this morning.

     

    It must've been *my* special dinner last night that's helping me here this morning!  Wink

    Wednesday, March 12, 2008 3:07 PM
  • Depending on how large is the data set being inserted, executing the load in one single batch in a execute sql task could be a problem, as all rows will be committed in one transaction. In the data flow you have control over the size of each batch.

    Wednesday, March 12, 2008 3:08 PM