Conditional lookup
Posted: Wed Aug 05, 2009 5:31 pm
Hi,
Say my source data has 1M records. 10 records have flag = 1, the remainder have flag = 0.
The two scenarios go down separate update links to the target table.
Flag = 0 is Update existing or insert new.
Flag = 1 is Update existing only.
Problem:
Flag = 1 implies that there is an existing record in the target table (Flag = 1 is a "backdated" record). If there isn't an existing record, I need to raise a warning. DS silently ignores the fact there isn't an existing record when the update action is Update existing only.
Potential solution:
"Manually" code a lookup against the target table. However, the lookup will be against a view and not perform well. I would like to do the lookup only when flag = 1.
My approach:
From an upstream transform, use constraints to split the stream into Backdated and Not_Backdated links. On the Backdated link, use another transform to lookup against the target table. Set a warning flag accordingly based on lookup results. Use the link collector to merge the two streams back together. Load the target table from the merged stream.
To do this, I have to set job properties to enable row buffer, interprocess. I've upped the buffer size from 256 to 1024KB.
Enabling this job property adversely affected performance, but this was the only solution I could think of.
1) Is this the best approach to the problem statement?
2) Are there options (such as increasing the row buffer even more) that I can use to optimize performance?
Thanks,
Scott
Say my source data has 1M records. 10 records have flag = 1, the remainder have flag = 0.
The two scenarios go down separate update links to the target table.
Flag = 0 is Update existing or insert new.
Flag = 1 is Update existing only.
Problem:
Flag = 1 implies that there is an existing record in the target table (Flag = 1 is a "backdated" record). If there isn't an existing record, I need to raise a warning. DS silently ignores the fact there isn't an existing record when the update action is Update existing only.
Potential solution:
"Manually" code a lookup against the target table. However, the lookup will be against a view and not perform well. I would like to do the lookup only when flag = 1.
My approach:
From an upstream transform, use constraints to split the stream into Backdated and Not_Backdated links. On the Backdated link, use another transform to lookup against the target table. Set a warning flag accordingly based on lookup results. Use the link collector to merge the two streams back together. Load the target table from the merged stream.
To do this, I have to set job properties to enable row buffer, interprocess. I've upped the buffer size from 256 to 1024KB.
Enabling this job property adversely affected performance, but this was the only solution I could think of.
1) Is this the best approach to the problem statement?
2) Are there options (such as increasing the row buffer even more) that I can use to optimize performance?
Thanks,
Scott