Low performance in the access the Informix

chulett · Post by **chulett** » Wed Mar 07, 2007 8:15 am

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.

chulett · Post by **chulett** » Wed Mar 07, 2007 11:47 am

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.

DSguru2B · Post by **DSguru2B** » Wed Mar 07, 2007 11:48 am

Load your file into a temp table and do a sql join. See if that works out for you.

chulett · Post by **chulett** » Wed Mar 07, 2007 11:54 am

Already suggested that.

DSguru2B · Post by **DSguru2B** » Wed Mar 07, 2007 11:55 am

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...

RAI ROUSES · Post by **RAI ROUSES** » Fri Mar 09, 2007 7:15 am

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

chulett · Post by **chulett** » Fri Mar 09, 2007 7:58 am

Excellent. :D