Inside a Lookup Transformation: Avoiding Performance Pitfalls

Inside a Lookup Transformation: Avoiding Performance Pitfalls

How Lookup Transformation Works

The key aspects of a Lookup Transformation are the
SQL that pulls the data
Cache memory/files that holds the data
Lookup Conditions that query the cache memory/file
Output Columns that are returned back
When the first record from the source, hits the lookup transformation, the cache (index & data file) starts to build in sequential caches. From version 8 for connected lookup we can configure to create lookup caches concurrently
The data is fed to cache from the lookup data source like database through the default SQL Query or the Override Query
Based on the memory availability the index and data files are created in memory and written to disk as a file
The index file has the Lookup ‘condition’ columns and the data file has the Lookup ‘output’ columns
The ‘index file’ has the pointers to the data in the ‘data file’
Once the lookup cache is created, the lookup transformation is ready for querying
When the other input records come in, the ‘index file’ is queried and the corresponding ‘output’ data in the ‘data file’ is determined
The searching in the ‘index file’ and the location of the data in the ‘data file’ involves loading these files into memory. Based
on the availability of the memory, disk swapping happens
Why Sort the data that is provided to cache by the lookup condition columns?
On sorting the data by the lookup condition columns, the index file building process is much faster. This is because the search tree of the Index file would be built faster with lesser node realignment
Why Sort the incoming data (Source Qualifier) by the lookup condition columns ?
If the subsequent input source records come in a continuous sorted order then the hits of the required index data in the memory is high and the disk swapping is reduced
Can a separate disk reader/writer improve Lookup Cache performance?
Having a dedicated separate disk ensures a reserved space for the lookup cache files and also improves response of the writing to the disk and reading from the disk.

Download

Download full paper At
http://www.enjineer.com/forum