Sparse Lookup

gbraju2003 · Post by **gbraju2003** » Tue Aug 09, 2005 2:43 pm

Hi,

I have some problem with the sparse Lookup,
I have a table with Millions of Records, So if i go for Normal Lookup, it will be slow, So i went for Sparse Lookup.
But it is not fetching the record, instead it is always retrieving '0'.

Pl help me out in this, If i use Normal it is working Fine.

Thanks in Advance
Basi

vmcburney · Post by **vmcburney** » Tue Aug 09, 2005 7:27 pm

Sparse lookups have a limitation that normal lookups do not have, they need the lookup key fields to have exactly the same names. If you switch your database stage to sparse and then look at your lookup stage properties you no longer see the lines that join your primary columns to your lookup key columns. The database stage performs this join automatically.

Make sure you have the correct column names and the correct lookup columns defined as key.

tusharzade · Post by **tusharzade** » Wed Aug 10, 2005 12:40 am

But if you use the sparse look up, then process will be slower as it has to hit the database for every record. But if you use the normal lookup then it will cache all the record and then do the look up.

dsxdev · Post by **dsxdev** » Wed Aug 10, 2005 2:23 am

Hi
In case you are using auto generated query in database stage with lookup type as sparse then the column names of columns used for lookup need to be same in both reference and input link.
They also need to be checked as keys in columns tab of reference link.
In a sparse lookup you have to have the column names same for the columns being used as lookup keys.

In case you are using a user defined query then the names can be different.

Before going for sparse lookup you can check if join will be a better option in this case. If the no of records comig fro input is very very less compared to data from reference link then yuor choice is good.
If the coming from input is also considerable then evaluate the option of using Join Stage.

nrevezzo · Post by **nrevezzo** » Thu Aug 11, 2005 2:49 pm

We ran into a problem with the Oracle sparse lookup when returning a numeric field for matched records the value was always zero. I just received a patch from Ascential support to fix this problem but have not applied it yet. The patch is for v7.1. If the field being returned is char then the proper value is returned.
I believe the ecase is 61141.
This is not a problem in v7.5.