Post reply

Several ways to do it, which is better?

  • April 11, 2008 at 4:45 am

    #124769

    Hi,

    I'm experienced in etl but new to ssis. So I would like to have your advice.

    In my scenario I want to lookup id's from several reference tables.

    Solution 1:

    Use a lookup Operator for every id

    Solution 2:

    Join the "maintable" with the reference tables and derive the id's from the output

    I think, that solution 2 will be faster? In past I worked with ELT Tools and unfortunately I don't know yet if i can transform my experiences to ssis.

    Can you give me some explains or references to documents about performance issues / best practices?

    Thanks,

    Tobias

  • April 11, 2008 at 9:22 am

    #801578

    It's going to depend on the amount of data you are importing, the amount of data in the lookup tables, and number of lookups, server configuration, and a host of other things.

    I typically use lookups (even a lot of them) if the lookup tables are relatively small or on servers other than my destination server. When I have a really big table to do a lookup in, I typically stage both data sets, create appropriate indexes, and use T-SQL.

    Remember SSIS is row-by-row processing. It is really good at it. T-SQL in SQL server is bad at row-by-row processing but really good at set-based operations. Try to use the tool that works for your current situation and don't be afraid to mix them.

    Also take note that the lookup component is case-sensitive in it's join and very sensitive to white space and data types.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply