SSIS Lookup Component Performance Sorting Lookup Query

Question

I'm attempting to use the SSIS 2008 lookup component in full cached mode with a T-SQL Query. Is there any benefit to using a ORDER BY clause in the T-sql.

Here is a example:

SELECT
 FooFK
,FooValue
FROM dbo.Foo
ORDER BY Foo;

In this case, I'm using FooValue to lookup the value of FooFk.

Also will multiple Lookups component run in parallel? I think this was the case in 2005.

Answer 1

I never use an Order By in the tsql of a lookup and have received really good performance. The datasets I typically work with have been huge, too (couple of million rows). Having said all that, I would just do some peformance testing (lookup without the Order By and one with it) to see what sort of performance you get using either one. To answer your second question, the answer is 'yes' lookups will run in parallel since they are considered synchronous, non-blocking transformations: Jorge Klein Blog