March 15, 2010 at 2:50 pm
What would be the faster and more efficient method to use for this situation?
The business wants a table containing distinct acct_nb and addr_update_indicator with values 'Y' and 'N'.
The Address Update Table is by acct_nb (single occurance) and all I need to do is select all the rows from it, which total to about 20 million.
This table tells if the account is eligible to see if it's address should be updated.
I need to combine this data with another table that has 34 million rows
and single occurance of acct_nb. This table contains address update eligible and non-eligible accounts.
The same acct_nb may exist on both source tables.
Table 1: 20 million rows
Table 2: 34 million rows
New result table: contains 34 million distinct acct_nb and addr_update_in set to 'Y' or 'N'.
Should I load the 20 million accounts into my destination table first and then use a Lookup Transformation to get the non-duplicate accounts from table 2 loaded or should I do a full outer join between the 2 tables?
I can't use a UNION statement without creating additional staging tables make the make the columns and datatypes match. It's doable, but if I can accomplish an efficient method to get the desirable results with less work, that would be the preferred method.
Is there a more efficient way to accomplish this in SSIS instead of using the Lookup or full outer join?
Thanks in advance.
March 15, 2010 at 3:29 pm
It would depend on how you've indexed your tables. I would prefer to use the lookup transformation against an indexed column. If all you need to do is check for the existance of a value in the table and that column is indexed, the lookup should be faster.
March 16, 2010 at 8:31 am
The column being used is the acct_nb which also is indexed on both source tables and the destination table. Thanks!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply