DSXchange

What is a Sparse Lookup? Why and when can we use it?

Thanks

Hi Sami,

this is what i have found in help. hope this can hint you

Lookup Type. Where the DB2 stage is connected to a Lookup stage via a reference link, this property specifies whether the DB2 stage will provide data for an in-memory look up (Lookup Type = Normal) or whether the lookup will access the database directly (Lookup Type = Sparse). If the Lookup Type is Normal, the Lookup stage can have multiple reference links. If the Lookup Type is Sparse, the Lookup stage can only have one reference link.

As for the why and when, refer here.

Sparse lookup is like firing one-by-one query on the DB table to fetcht the result.
The select fields include all the fields that we are fetching from the lookup table and where field and value include the metching field and the value for the metching input fields.
I hope this clerifies the issue.

The post that I embeded has comments from our very best, Ray and Vincent. Adhere to their warning on when to use it, else there will be bottlenecks

When you have large amount of data comming from source its better to use normal lookup and if you are having incremental comming better go for sparse so that the performance will be good and when you use sparse look up use ORCHESTRATE.Key_field_name= Key_field_name. in where condition.

As recommended in document, go for sparse in 1:100 ratio. That is if you have input records 100 times less that the lookup data. In other way, very large data to be loaded into physical memory where as fewer times of hits are required to the lookup table.

Due to some reason, I could not locate the option to change the Normal lookup into Sparse.
Could you pls help me on locating it.

Where (in which stage type) did you look?

if you open the Oracle_Enterprise stage you can find it in "Look Up Type" drop-down.

The "Look Up Type" dro-down has 2 options "NORMAL" & "SPARSE"

HTH

vick

I doubt that the OP is using an Oracle Enterprise stage, as DB2 was specifically mentioned.

Even the DB2 has the same option to convert to 'SPARSE'.

The only way a the sparse lookup can be performed is that you connect the DB2 stage dirctly to the lookup(without any other stage in between DB2 and lookup).

What if someone other than u wants to perform a sparse lookup?

I didn't got the exact meaning? Can you be more precise and descriptive!