DSXchange

Which one is Faster : Normal Lookup or Sparse Lookup?

can you please tell which lookup is faster. My understanding is Normal lookup would be faster since there is no hit with DB.

Sparse lookup should only be used for small number of input records. It is inefficient if the volume of incoming records is high. So it should only be used in situations where the volume of incoming records is less versus the volume of reference records is high.
Example : if you are looking up few records against a large fact table where reading the entire fact table in normal lookup would be inefficient.

Faster?

It depends.

A normal lookup has to load the entire reference data set into memory before even the first lookup can occur. In some cases the reference data set must be read from a database.

A sparse lookup has to go to the database for every single lookup.

But for a sufficiently small number of lookups, sparse may actually be faster.

There are other considerations, too. For example if you want to expose a job as an "always on" web service, then sparse lookup should be preferred so that current information is always provided.

Either one can be faster.