What is the difference between lookup and sparse lookup?
Answer Posted / subhash
Normal lookup will provide data for an in-memory look up
whereas Sparse lookup will access the database directly.
Normal might provide poor performance when the reference
data is huge as it has to load large data into memory.
In such scenarios a join stage may work better(It will put
the data onto an internal dataset file for each link,
and then do the join based on the data provided). If your
stream/source data is small then a sparse lookup is
suggested
as a row is retrieved from the database for each input row
instead of the full reference data being brought into
memory.
Sparse lookup sends individual SQL statement for every
incoming row (If stream/source data is huge you can imagine
the number
of times it has to hit DB and hence the down side on
performance). It can be used when you want to get the next
sequence
number from your database (Again expensive overhead on your
job as noted before). Also note that sparse lookup is only
available for DB2 and Oracle. Normal Lookup stage can have
multiple reference link but Sparse can only have one
reference
link. When normal is used it is a good practice to choose
less volume data as reference data.
Is This Answer Correct ? | 15 Yes | 1 No |
Post New Answer View All Answers
How to reverse the string using unix?
How a server job can be converted to a parallel job?
Hi,can any one please mention list of dimension and fact tables for Sales and Distribution System for a Pharmacutical company.
Define repository tables in datastage?
How to perform incremental load in datastage?
What is a merge in datastage?
Hi guys, Please design a job for dis requirement with derivation(solution). my source table like dis. emp_no qualification 1 a 1 c 2 a 3 c 3 b To loaded to target like dis emp_no qualification 1 b 2 b 2 c 3 a my requirement is every employer have three qualifications i.e a,b and c. what qualification missed in source table that will be move to target systems. Hope u got it the requirement. Right Thanks.
What is orabulk stage?
Can you define merge?
How one source columns or rows to be loaded in to two different tables?
Difference between sequential file and data set?
What is the flow of loading data into fact & dimensional tables?
Can you explain engine tier in information server?
Differentiate between hash file and sequential file?
How do you import and export data into datastage?