Hi,
We are performing a sparse lookup on a table, with input data around 1 Million records. The performance of the lookup is very very slow.
The reference table has got around 500Million records. and the table is indexed.
Can any one suggest a solution to resolve this problem?
Thanks !!!
Sparse lookup Perforamance
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Think about how a sparse lookup works. It will necessarily be slow.
Alternatives include doing the join the the database, if both data sources are in the same database instance, or using a Join stage.
Are all 500 million rows needed? You might create a "bucket table" containing only the keys in the source data then, when performing the lookup, do it against the inner join of the bucket table and the reference table. This might even provide a small enough set of records that you do not need to use sparse lookup and will be able to use a normal, memory-resident, lookup.
Alternatives include doing the join the the database, if both data sources are in the same database instance, or using a Join stage.
Are all 500 million rows needed? You might create a "bucket table" containing only the keys in the source data then, when performing the lookup, do it against the inner join of the bucket table and the reference table. This might even provide a small enough set of records that you do not need to use sparse lookup and will be able to use a normal, memory-resident, lookup.
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.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
The sparse lookup is a last resort, when you can't find any other way.
Try the "bucket" technique that I suggested - you may be able to use a normal lookup then.
Try the "bucket" technique that I suggested - you may be able to use a normal lookup then.
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.