TYPES OF LOOKUP
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 40
- Joined: Thu Jul 10, 2008 12:45 pm
TYPES OF LOOKUP
What is the difference between SPARSE and Normal lookup and what situation we are supposed to use them.
Srikanth Reddy
Integration Consultant
Integration Consultant
The "normal" lookup takes the complete contents of the link and loads it to a virtual dataset in memory. Each row being looked up is already in memory, so is quite fast.
"Sparse" lookups actually do a lookup/select for each row separately.
Each is used in a different scenario. If your data has 100 records and your lookup data is 10,000,000 rows you had best use a Sparse lookup; if the row counts are reversed you had best use a normal lookup.
Another aspect is that a normal lookup takes a snapshot of the lookup contents at the beginning of the run; if the reference data changes during the runtime of the job then these changes will not be reflected in the lookup data. Sparse lookups, on the other hand, will reflect such runtime data changes as soon as they occur.
"Sparse" lookups actually do a lookup/select for each row separately.
Each is used in a different scenario. If your data has 100 records and your lookup data is 10,000,000 rows you had best use a Sparse lookup; if the row counts are reversed you had best use a normal lookup.
Another aspect is that a normal lookup takes a snapshot of the lookup contents at the beginning of the run; if the reference data changes during the runtime of the job then these changes will not be reflected in the lookup data. Sparse lookups, on the other hand, will reflect such runtime data changes as soon as they occur.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: