SSIS 2008查找缓存…

集成服务2008年有一个新的转换,包括一个缓存选项,允许查找执行比以前要快得多。有三种模式缓存和一个新的缓存连接管理器。让我们来看看…当添加一个查找转换到数据流任务请求如其名,一个查找。基于查找表匹配的列,用于检索数据流可以使用的其他数据列。例如,基于一个雇员Id我们可以查找电子邮件地址基于Id并添加到数据流。有不同的流动和NoMatch相匹配。如果我们处理大量数据,查找可能会被迫做重复的数据库查询创建一个潜在的瓶颈。现在在SSIS 2008有一个缓存模式,默认为全部。这意味着查找执行查询之前查找转换本身,结果放入内存中缓存。SSIS将使用缓存来满足实际的查找提供显著的性能改进。 Of course, there must be enough memory available to store the results of the lookup query and it must be recognized that, as with all caching options, the data in the cache may become stale as updates are made to the underlying data. To optimize memory usage, there is another Caching Mode, namely Partial. This mode will fill the cache based on the matches only so will save memory usage but will issue more database calls than Full mode. The third mode is None which implies no caching. Well, almost no caching. It will at least cache the last lookup in case there are repetitive lookups in order. A further new option is an extension of the Full Cache mode which allows you to persist the cache by using a Cache Connection Manager. This option uses a .caw file that is written to disk and then read into memory. This gives improved performance with the added benefit of being able to share the cache between multiple tasks and even other packages. Just how much performance gain you will get out of the new Lookup transformation is all down to detailed testing using tools such as the SQL Profiler to trace and verify, but we now have some viable options to limit the Lookup bottleneck. Cheers Brian SSIS 2008 Lookup Transformation: http://msdn.microsoft.com/en-us/library/ms141821.aspx SSIS - Lookup Cache Modes - Full, Partial, None: http://www.ssistalk.com/2009/09/04/ssis-lookup-cache-modes-full-partial-none/