Lookup Performance Issue
Moderators: chulett, rschirm, roy
Lookup Performance Issue
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
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
What stage type is being used for the lookup?
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
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).
Load the keys into a work table and join them to your source table(s).
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Get more space on your server.paranoid wrote:I think hashed file takes up lot of space in my server which i dont have. Is there any other solution?
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Charter Member
- Posts: 560
- Joined: Wed Jul 13, 2005 5:36 am
- Location: Ohio