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

 All Forums
 SQL Server 2005 Forums
 SSIS and Import/Export (2005)
 SSIS Foreach Loop Caching

Author  Topic 

Brittney10
Posting Yak Master

154 Posts

Posted - 2013-08-02 : 11:45:29
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?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-03 : 01:42:14
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
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -