Sparse lookup Perforamance

bucks · Post by **bucks** » Sat Feb 23, 2008 3:20 pm

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

ray.wurlod · Post by **ray.wurlod** » Sat Feb 23, 2008 3:24 pm

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.

bucks · Post by **bucks** » Sat Feb 23, 2008 3:27 pm

Thanks Ray,
Yes, I did that. but doing an inner join would fetch all the data in the table and then output the data to output link. This approach would take time.

But then what is the use of the sparse lookup, if this takes so much time?

Thanks again for the response

ray.wurlod · Post by **ray.wurlod** » Sat Feb 23, 2008 3:29 pm

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.

bucks · Post by **bucks** » Sat Feb 23, 2008 3:31 pm

Okays, will try that

Thanks