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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.