Lookup Performance Issue

paranoid · Post by **paranoid** » Thu Jun 05, 2008 6:47 am

Hi,

I am construction a DS job that uses two lookups that contains 7 million records in each but my source is having only 150 records. When i run this job it is taking a lot of time as it needs to parse all those 7 million records in the lookuop. I have already indexed the columns in the WHERE clause.

Could any one of you suggest me a good way of increasing the performace?

Thanks

Sue

ArndW · Post by **ArndW** » Thu Jun 05, 2008 6:51 am

What stage type is being used for the lookup?

paranoid · Post by **paranoid** » Thu Jun 05, 2008 6:55 am

I am using a transformer stage and the output stage is XML output stage.

Thanks

paranoid · Post by **paranoid** » Thu Jun 05, 2008 6:56 am

I am using Oraclestage for lookup.

chulett · Post by **chulett** » Thu Jun 05, 2008 7:09 am

A typical solution would involve building a hashed file from your Oracle source based on the natural keys in your data, then use the hashed file for the lookup. That way you don't pull 7M records into the hash but rather just as many of the 150 as you need.

Load the keys into a work table and join them to your source table(s).

paranoid · Post by **paranoid** » Thu Jun 05, 2008 7:17 am

I think hashed file takes up lot of space in my server which i dont have. Is there any other solution?

nivas · Post by **nivas** » Thu Jun 05, 2008 7:24 am

switch lookup and source positions if possible. I mean to say change the lookup to source and source to lookup as the present source has only 150 records.

chulett · Post by **chulett** » Thu Jun 05, 2008 7:48 am

paranoid wrote:I think hashed file takes up lot of space in my server which i dont have.

No, they don't - not when handled properly.

ray.wurlod · Post by **ray.wurlod** » Thu Jun 05, 2008 2:03 pm

paranoid wrote:I think hashed file takes up lot of space in my server which i dont have. Is there any other solution?

Get more space on your server.

If you haven't got enough space for a moderately sized hashed file you are going to run out of space somewhere, sometime in your ETL processing.

paranoid · Post by **paranoid** » Fri Jun 06, 2008 6:46 am

Hey thank you everyone for replying to this message.I have requested to increase the space in my server.Please close this ticket.

Have a great day!!

chulett · Post by **chulett** » Fri Jun 06, 2008 6:51 am

Excellent.

ps. It's not a ticket. And you close it by marking it as "Resolved" which you've done. So we good.

Krazykoolrohit · Post by **Krazykoolrohit** » Mon Jun 09, 2008 10:39 am

won't using a left outer join help here?

Ofcourse it will help only after the oracle stage has been replaced with the hash file as suggested by ray.

But, I think you can try using the join and run the job till the time you get additional space.