Be selective about the lookup columns
Most designers would use the obvious default behavior of the Lookup transform, selecting a table or view to look up in. However, choosing a table will be interpreted as “SELECT *”. This will cause the transformation to fetch un-needed data. That is a waste of effort, memory and resources. I recommend choosing “Use results of an SQL query” instead of naming a table, and in the query selecting only the columns that are used.
Enable memory restriction
Two things happen when memory restriction is enabled on a Lookup: First, the amount of memory that the Lookup is allowed to use is limited. A cache policy is used and new rows are added to the cache on demand. Second, the new rows are added to the cache individually. In other words, SSIS will query the relational database whenever data for a row cannot be located in the internal cache kept by SSIS. These are single-row queries, unlike the large set-based table queries that occur when memory restriction is not enabled. The benefit of enabling memory restriction is that large lookups can be performed which might not be possible otherwise. The cost is that single row queries are used, which collectively are usually slower than a single table query. However, I have found that this performance cost is not always as bad as one might expect, if appropriate indexes are present on the lookup table.
Most of the time you will get good performance from Lookup transforms with default settings. Sometimes, lookups can become significant in the performance of an SSIS package especially if processing a large amount of source rows. I have given a few tips that I have found make the biggest difference in lookup performance. I hope you find them useful in your package designs.
- Be selective about the columns you request in a lookup
- Enable memory restriction if the lookup is using too much memory
- Be sure you have an appropriate index on the lookup table when using memory restriction
- Consider enabling memory restriction if the lookup refers to a small number of rows from the lookup table