在 SSIS 的数据流组件中,SSIS 引擎使用 Merge Join 组件和 Lookup 组件实现 TSQL 语句中的 inner join 和 outer join 功能,Lookup 查找组件的功能更类似 TSQL 的 Exists 关键字,只检查数据是否存在。在 SSIS 引擎中,任何流经数据流(Data Flow)组件的数据都会被加载到服务器内存的数据缓冲区中,数据缓冲区能够容纳的数据量决定了转换组件的性能。
一,转换组件的结构
1,Lookup 转换组件有一个输入(Input),一个查找表(或叫缓存表,引用表),映射关系和多个输出(Output)
映射关系是指 Lookup 转换组件的输入(Input)列和查找列之间的相等关系,定义了输入和引用表之间按照什么条件进行匹配,相当于定义 Join 子句的 On 条件;在创建映射关系时,用户需要显式指定一个或多个映射关系,就是说,用户需要指定哪些 Input 列和查找列之间具有相等关系。
Lookup 组件查找的过程是:对于输入(Input)中的每一个数据行,根据映射关系,对查找表进行全表查找;如果该数据行能够在查找表中找到相应的键值,那么该数据行匹配成功,从 "Lookup Match Output" 路径输出到下游组件;如果不能在查找表中找到相应的键值,那么该数据行匹配失败,从 "Lookup No Match Output" 路径输出到下游组件。
由于输入中每一个行数据都会查找整个缓存表,因此,如果将查找表数据缓存在内存中,能够提高 Lookup 组件的查找性能,Lookup 转换组件提供三种缓存模式来处理查找表的数据:全部缓存在内存,部分缓存在内存中,每次都从数据源中读取。如果查找表数据量少,请全部缓存在内存中,以提高 Lookup 组件的转换性能。
Lookup 组件在 Full Cache 模式下是无阻塞转换,只有 Lookup 转换组件在加载缓存表数据时,它才会阻塞数据流。只有当缓存表数据加载完成之后,查找转换组件才开始运行。一旦缓存数据加载完成,数据以无阻塞的流式来处理数据。
2,Merge Join 转换组件有两个有序的输入(Sorted Input,使用 Sort 组件排序,或者在数据库中使用 Order by 子句排序)和一个输出(Output)
在 Merge Join 转换组件配置联接条件(inner join,left join 和 full join),执行连接查询之后,输出相应的数据。下游组件可以使用 Conditional Split 转换组件,获取匹配成功或匹配失败的数据。Merge Join 转换组件没有缓存数据。
二,Lookup 转换
1,流的特性
Lookup 转换是非阻塞转换,具有流式转换的特性,能够边加载数据,边对数据进行转换处理,这意味着,当新的数据行进入 Lookup 转换组件时,已经被处理完成的数据行会被传递到下游组件,而不会被拦截,就像水流一样,绵绵不绝,直到所有数据处理完成。
当 Lookup 转换组件处于 Full Cache 缓存模式时,Lookup 转换组件在将缓存表加载到内存中时,会阻塞数据流,直到所有的查找数据都加载到缓冲区后,才开始真正执行数据流任务(Data Flow Task),因此,为了提高查找转换的性能,确保将小表作为缓存表(设置为 Full Cache 模式),而将大表的数据以流式输入到 Lookup 转换组件中。
当 Lookup 转换组件处于 Partial Cache 或 No Cache 缓存模式时,Looup 转换组件被识别为基于行(row-based)的转换,流经 Lookup 转换组件的数据行,需要与一个外部输入进行交互,从而被逐一处理。鉴于基于行的处理过程,在大多数情况下无法跟上数据流处理的速度,因此,缓冲区将被拦截阻塞,直到 Lookup 转换组件处理完缓冲区中的所有数据,所以,当 Lookup 转换组件处于 Partial Cache 或 No Cache 缓存模式时,通常认为 Lookup 组件具有半阻塞性。
2,缓存模式(Cache Mode)
Lookup 转换组件有三种缓存模式(Cache Mode):Full Cache,Partial Cache 和 No Cache。
全缓存模式(Full Cache)是指 Lookup 转换组件将缓存表中的数据全部加载到内存的数据缓冲区中,另一个输入中的每一行数据都会流经缓冲区,执行联接操作。
无缓存模式(No Cache)是指 Lookup 转换组件对上游输入的每个数据行,都会执行一次查询,检查数据是否存在于缓存表中。 在 No Cache 模式下,当每个输入行流经 Lookup 转换组件时,该组件向数据库中的引用表发送一条请求,查看键值是否匹配,这种方式性能非常低下,速度慢。
部分缓存模式(Partial Cache)是指 Lookup 转换组件在 MaxMemoryUsage 属性限制的内存使用量下,将最近使用过的数据缓存到内存中,一旦缓存增长过大,最少使用的缓存数据将会被丢弃。如果引用表数据量太大,而无法将其所有数据全部加载到缓存中,可以选择 Partial Cache 模式。
当 Package 启动时,与 No Cache 模式一样,不会将数据预先加载到 Lookup Cache 中,当每个输入行进入组件时,该组件使用指定的联接键以及指定的查询来尝试查找匹配的记录。如果找到匹配项,那么及时将查找到的键值添加到缓存中,如果相同的键值再次进行查找,那么就可以从缓存中获取匹配键值,从而节省了访问外部输入源的查询时间。如果在缓存中没有找到匹配的键值,那么组件将访问外部输入源,进行查询,如果外部输入中也没有,那么键值不匹配。
MaxMemoryUsage 属性指定 Lookup 转换组件在 Partial Cache 模式下所使用的最大内存。
三,Merge Join 转换
1,Merge Join 是半阻塞转换
在向下游组件传递数据之前,Merge Join 转换组件需要将数据流拦截在缓冲区中一段时间,直到来自两个输入的键值匹配成功,Merge Join 转换组件才将数据行向下游组件传递。
2,Merge Join 使用少量的内存
相比于 Lookup 转换组件,Merge Join 转换组件只使用较少的内存,基本上不会缓存数据,因为只需要维护内存中用来联接两个输入所需要的少量数据。当内存容量有限或者,输入的数据量过大时,Merge Join 转换组件是一个非常有用的组件,但是,Merge Join 转换组件有一个前提条件,输入的数据流必须是有序的,由于 SQL Server 数据库引擎的排序功能非常强大,因此,推荐将 Merge Join 的输入数据流在 SQL Server 数据库中进行排序,避免使用 Sort 组件对数据流进行排序。
四,缓存连接管理器
Lookup 转换组件是唯一使用 CCM(Cache Connection Manager,简称 CCM)对数据进行缓存的组件,CCM 能够从任意数据源中填充 Lookup 转换组件的缓存。如果 Lookup 转换组件处于 Full Cache 模式下,那么使用 CCM 加载缓存数据将会提高转换性能。在同一 Package 中,如果多个 Lookup 转换组件使用相同的缓存数据集,使用 CCM 缓存数据,这些 Lookup 转换组件可以共享相同的缓存,这样,就不需要多次加载相同的缓存数据。
五,在数据源组件中,指定数据流是已排序的
step1,有序的输入
在获取数据时,使用 Order By 子句对数据进行排序,使数据在进入数据源组件时,是已经排过序的。
- from dbo.data_source
- order by UserID asc,ProfileID desc
step2,设置 IsSorted 属性为 True
打开 OLE DB Source 数据源组件的高级编辑器(Advanced Editor),点击 "OLE DB Source Output",将 "Common Properties" 列表中的 IsSorted 属性设置为 True。该属性不会对数据进行排序,只是标识数据流是有序的。
Step3,SortKeyPosition 属性标识已排序的列和其排序的方向
在 SortKeyPosition 属性中,正表示升序,负表示降序,绝对值表示位置序号,位置从 1 开始,依次递增。
打开 "Output Columns" 输出列列表,将 UserID 的 SortKeyPosition 属性设置为 1,ProfileID 的 SortKeyPosition 属性设置为 - 2。
参考文档:
来源: http://www.cnblogs.com/ljhdo/p/5509837.html