Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Register Now and get your question answered!
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SSIS and Import/Export (2005)
 SSIS Foreach Loop Caching
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Posting Yak Master

154 Posts

Posted - 08/02/2013 :  11:45:29  Show Profile  Reply with Quote
I have a ForEach file enumerator in my Control Flow that contains several Data Flows. One specific Data Flow has to do a lookup on 10 million records to determine what to do with the data (i.e. Update or Insert). When testing with about 10 files going through the ForEach loop, I'm seeing the 10 million records are being cached each time during each loop. I want to minimize the amount of time it takes to process these files, so performance is very important. What can I do to reduce/optimize caching on this lookup of 10 million records?

Very Important crosS Applying yaK Herder

52326 Posts

Posted - 08/03/2013 :  01:42:14  Show Profile  Reply with Quote
why is it caching same records again and again? are you sure the values coming are the same? Ideally if values are same you dont need to cache it again and again. If values are changing in each loop then there's little advantage you could take out of cacahe as it has to read in new values anyways for matching.

SQL Server MVP
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000