September 25, 2009 at 12:53 am
Which is bettter in perfomance ,
Look up or adding a left join to Sql query in Oledb source itself .
i have 10 Look up to be used in a single package each around 50000 rows
September 25, 2009 at 1:25 am
I would say LEFT JOIN, but it depends on the volumes and indexes of the tables you are joining.
With lookups, you issue one query for each row, with the join you leave to the db engine the task of choosing the best plan to "lookup" the rows. It knows better how to handle data, it is designed for that.
-- Gianluca Sartori
September 25, 2009 at 8:21 am
If you can use a LEFT JOIN then I'd suggest that at is a set-based operation, while the Lookup Component is RBAR. Granted SSIS is designed to be fast with RBAR processing, but I'd bet the LEFT JOIN will greatly improve performance of the package.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 25, 2009 at 9:40 am
Also be aware that SSIS lookup is by default case-sensitive , there are work-arounds available, So you may not get the same results as a Left-Join
September 25, 2009 at 10:11 am
I agree that generally the LEFT JOIN is the better performing option. You'll want to consider using the SSIS lookup in cases where you want to do some alternative processing to your unmatched rows, such as sending them to an error queue or output file.
hth,
Tim
Tim Mitchell
TimMitchell.net | @tmitch.net | Tyleris.com
ETL Best Practices
September 25, 2009 at 10:54 am
steveb. (9/25/2009)
Also be aware that SSIS lookup is by default case-sensitive , there are work-arounds available, So you may not get the same results as a Left-Join
Great point about case-sensitivity. I was burned by that in the past.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 25, 2009 at 11:09 am
I agree. Getting the data from the OLE DB source component here may be better. Typically, the source data is comming from a seperate system then where you are doing the lookups so you would need to use the Lookup Transformations to look up values in the destination system based on values from the source system.
In the case where you can get all of the data you need in one shot, go for it! It will, generally speaking, perform better, the package will be easier to maintain and less complex, and you'll not have to watch out for the gotcha's that come along with the ETL process.
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply