Page 1 of 1
Posted: Wed Mar 07, 2007 8:15 am
by chulett
Don't use a database stage for a lookup. Period. I don't care what database we're talking about, it's the least performant way to do that.
Use a hashed file. They are the heart and soul of Server jobs, so leverage them. And before you ask - no, there's absolutely no reason to put all 20M source records in it first. Put
only what you need there
for each run. Load the keys from your source file into a work table and use that to constrain your build sql for the hashed file.
Posted: Wed Mar 07, 2007 11:47 am
by chulett
You missed my point... and it's not impossible, but if you want to believe that, fine.
Work on tuning the reference lookup query then, perhaps an index is in order to supprt the query? I don't believe you have any choice but to use ODBC for Informix, so do your best to make the reference lookup query as 'performant' as possible.
Posted: Wed Mar 07, 2007 11:48 am
by DSguru2B
Load your file into a temp table and do a sql join. See if that works out for you.
Posted: Wed Mar 07, 2007 11:54 am
by chulett
Already suggested that.
Posted: Wed Mar 07, 2007 11:55 am
by DSguru2B
chulett wrote:
[/i] Load the keys from your source file into a work table and use that to constrain your build sql for the hashed file.
Ahh, now I see. I am going blind...
Posted: Fri Mar 09, 2007 7:15 am
by RAI ROUSES
Thanks chulett and DSguru2B, it reduces the process time. We first extract from Informix table the data that we need to flat file, and then pass to hash-file. It reduces our time in 40%.
Thanks for your support
Rai
Posted: Fri Mar 09, 2007 7:58 am
by chulett
Excellent. :D