April 11, 2008 at 4:45 am
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
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