SSIS Lookup performance improvement tips


Can anyone share your experience with me how to improve the LookUp on SSIS where currently I want to use the Lookup functionality in SSIS  - how ever it is slow thank SQL code.

Thanks in advance
ken hanseAsked:
Who is Participating?
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
It's no different than any other query.
Limit the number of rows in the Lookup.  If you are referring to a whole table, consider using a view instead with a WHERE clause that limits the rows.
Limit the number of columns in the lookup.  If you are referring to a whole table and only JOINing/checking a couple, then time is being wasted loading all of the other columns into memory that the Lookup doesn't actually use, so again use a view with only the necessary columns.

Give us some color as to the set of data that is the source, and the lookup, and we can provide more detailed answers.
ken hanseAuthor Commented:
problem is I'm on virtual environment where the process is very slow. is there any way we can improve performance
on the LookUp
Alpesh PatelAssistant ConsultantCommented:
In lookup use full caching. It will take time on first run but after you will get faster result.
ken hanseAuthor Commented:
what is the meaning after that - does the LookUp keep the data, how that work, what about new data?
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Ken - You'll need to paint a much more detailed picture for us to help you, as experts here are not likely to write a book for a 'help me my stuff is slow' question.

How many columns are in the lookup?
What's the source (SQL, Oracle, ...)
How wide in bytes is the lookup?
How many columns are being joined in the lookup?
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.