//
you're reading...
SQL Server

Optimizing Integration Services Lookups

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.

Conclusion

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
Advertisements

About ldgaller

Accomplished Data Warehouse Architect, DBA and Software Architect with over 15 years of professional experience and demonstrated success designing and implementing solutions that improve business functionality and productivity. Highly diverse technical background with proven ability to design, develop and implement technology on an enterprise level. I approach all projects with passion, diligence, integrity, and exceptional aptitude.

Discussion

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: