CODEWITHSUNDEEP
ssis
Chielus
Chielus

Reputation: 632

How to reuse the cache between several lookups

I have a SSIS2008 package that incrementally loads a table Clients with 10M records and a table ClientRelations with 6M records. A clientrelation is a relation between 2 clients.

In the RelatedClientsETL dataflow I get my relations from a flat file source, and I do 2 lookups on the same Clients table.

Would it be good to use the Cache Connection Manager here, or should I use the Partial Cache lookup option on both lookups? Or do you advise another option?

Memory of the Production SQL Server that executes this package = 8GB

Upvotes: 0

Views: 3759

Answers (1)

praveen
praveen

Reputation: 12271

Generally it is preferred to use Full Cache mode in lookup as this will reduce the database load ,thereby reducing the number of hits on the database .Since you have sufficient memory to hold the data ,i suggest you should use Full Cache .SSIS uses a buffer-oriented architecture to efficiently load and manipulate datasets in memory therefore the lookup tends to be faster in this mode .

Partial cache mode is generally avoided and also not a better choice.It is useful only in cases where you have less number of rows to be processed .It caches data only when there is a match found .There is no pre-execute caching done as in the case of Full Cache so loading time is fast but number of hits to the database is more .

CCM (Cache Connection Manager) is used when you want to re-use the cache to reduce database load but then again you need to test it your self by using OleDB connection vs CCM in lookup .

There is a free tool bidshelper which will help you monitor the performance of individual tasks else try to isolate your destination and other components except lookpup and source . Replace destination with RowCount transformation and measure the performance by checking in execution results tab for (time ,number of rows) .Perform it for both Full Cache and CCM

There is a whitepaper which superbly explains on overall SSIS performance

Upvotes: 1

Related Questions