Difference Between Normal Lookup and Sparse Lookup

Normal Lookup :- 

• Normal Lookup data needs to be in memory
• Normal might provide poor performance if the reference data is huge as it has to put all the data in memory.
• Normal Lookup can have more than one reference link.
• Normal lookup can be used with any database

Sparse Lookup :- 

• Sparse Lookup directly hits the database.
• If the input stream data is less and reference data is more like 1:100 or more in such cases sparse lookup is better.
• Sparse Lookup,we can only have one reference link.
• Sparse lookup,we can only use for Oracle and DB2.
• Sparse lookup sends individual sql statements for every incoming row.(Imagine if the reference data is  huge).

This Lookup type option can be found in Oracle or DB2 stages.Default is Normal.

courtesy :http://datastage4you.blogspot.in