Welcome to EMC Consulting Blogs

Jamie - I think it's interesting that the Connect link you posted is one of many that is closed as a duplicate issue. There are so many different Connect issues related to the limitations of the Lookup transform (and its inherent case sensitivity being a primary one) that it is often difficult to keep track of them. This one (http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=127229) is the "master" issue posting and has an average rating of 4.88 by 56 users, but has still been open for well over two years....

;-(

Actually, Jamie's linked Connect submission claims it is currently active...  Not too long ago, I created a new Connect submission for the purposes of fixing this in Katmai (because, at the time Jamie's submission was closed).  One way or another, I think they need to allow the developer an easy way to perform case-insensitive lookups.

It may be difficult for us to track the Connect submissions on this topic, but rest assured, the dev team is aware of the problems, not just from Connect, but also from my (our?) direct communications with the dev team.  

Alright, that didn't look right so I did some digging. It looks like this is what happened:

The issue that Matthew listed is being used as a "master tracking" issue internally. Its description doesn't actually look anything like it does on Connect. When those other connect items for lookup were closed as duplicate, whatever they were asking for was added to the master item. When one of our PMs posted that we were pushing this feature to the next major release, they probably didn't notice the description difference between the bug and the connect issue (the original text isn't immediately apparent in our system). They probably should have been clear about which features aren't in Katmai (ie. case insensative lookup)

I think we definitely need to improve the process here, and it's one of the things our PM team will be working on.

In the mean time, feel free to shoot me an email if the status of a connect item isn't clear. We will be going through and updating old connect items at some point, but right now we're focused on ironing out the remaining issues, and getting Katmai shipped. :)

Jamie,

I originally thought that also using the default of full caching would always be quicker (and also according to BOL this seems to suggest this) but my experience indicates this is not the case.

It is correct to say that by setting to partial or no cache 'This technique issues a query against the database for every single row in the pipeline and hence it will usually be an order of magnitude slower than using CacheType=Full' but note the word USUALLY.

The answer as always is it depends ie it depends upon the amount and type of data that you are using and also the % matches you get against your lookup.

Let me explain.

It depends upon the size of the reference data set and the data in the pipeline e.g. if reference data set is 100,000 rows, but only 20 rows come into the pipeline, full cache will have to cache 100,000 rows first.

Using partial cache will be much quicker since no data is cached first and even though the reference query has to run for each row coming through the pipeline it will be much quicker because it is likely that it finds these 20 rows without having to compare against all 100,000 rows.

Another impact will be whether the data coming in is unique or not ie how often a match will occur against the LKP reference data set. If it will occur often the full cache is less likely to be of benefit.

Another impact I found was the no. of Lookup transforms themselves in the pipeline ie. if you had mutliple Lookups in one DFT.

When any Lookups are used in the DFT, & the Lookups are set to use Full Cache mode, ALL the data from ALL the Lookup tasks is first cached before the pipeline is started so e.g.

if have lookup transform at beginning and end of pipeline it still caches all data for both before starting the pipeline (can see this happening in the progress tab).

This may not improve performance because say e.g. the first LKP in the transform it would be useful to have Full cache, but the pipeline diverges so that only a subset of the data initially coming in reaches the final LKP transform then having to cache all the data first may not have been an advantage.

Performance will also depend upon whether the LKP’s in the DFT use exactly the same reference data set or not since it will only be cached once with full cache.

Here are some results on testing that I did (times are for Full, partical and no cache):

No. rows in pipeline = 20,

No. rows in reference dataset = 100,000 58 secs 14 secs 14 secs In this case Partial cache is faster.

No. rows in pipeline = 40,

No. rows in reference dataset = 38,000 24 secs 34 secs -- In this case Full cache is faster.

No. rows in pipeline = 50,

No. rows in reference dataset = 65,000 2 mins 45 secs 47 secs In this case Partial cache is faster.

Therefore Jamie my testing showed that in fact it DEPENDS ! To never change the CacheType property from FULL is something I never do. I always test different scenarios with the data. You may surprisingly find that partial or no cache is indeed faster.

Glad to have provided feedback and thanks for all your blog entries. They have helped me a great deal in developing with SSIS.

The Lookup Transform does case sensitive string comparisons. This means that you need to a little bit