Page 1 of 1
Lookup Performance Issue
Posted: Thu Jun 05, 2008 6:47 am
by paranoid
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
Posted: Thu Jun 05, 2008 6:51 am
by ArndW
What stage type is being used for the lookup?
Posted: Thu Jun 05, 2008 6:55 am
by paranoid
I am using a transformer stage and the output stage is XML output stage.
Thanks
Posted: Thu Jun 05, 2008 6:56 am
by paranoid
I am using Oraclestage for lookup.
Posted: Thu Jun 05, 2008 7:09 am
by chulett
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).
Posted: Thu Jun 05, 2008 7:17 am
by paranoid
I think hashed file takes up lot of space in my server which i dont have. Is there any other solution?
Posted: Thu Jun 05, 2008 7:24 am
by nivas
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.
Posted: Thu Jun 05, 2008 7:48 am
by chulett
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.
Posted: Thu Jun 05, 2008 2:03 pm
by ray.wurlod
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.
Posted: Fri Jun 06, 2008 6:46 am
by paranoid
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!!
Posted: Fri Jun 06, 2008 6:51 am
by chulett
Excellent.
ps. It's not a ticket. And you close it by marking it as "Resolved" which you've done. So we good.
Posted: Mon Jun 09, 2008 10:39 am
by Krazykoolrohit
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.