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

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

Brittney10
Posting Yak Master

USA
152 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?

visakh16
Very Important crosS Applying yaK Herder

India
52309 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
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New 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