主页 / dba / 问题 / 176886
Accepted
Ryan
Asked: 2017-06-22 05:56:09 +0800 CST

优化 SSIS 包以减少查询

  • 772

我需要在客户下订单时将他们的订单与他们的“级别”（白银、黄金等）相关联：

CRM server::CRM db::CRM table
----------
CustomerID   PreviousLevel    NewLevel    NewLevelGrantedOn

Order server::Order db::Order table
----------
OrderID     CustomerID    OrderPlacedOn

在 SSIS 包中我这样做了：

  1. 对 Order db 执行 SQL 以提取订单并将它们放入对象变量中；
  2. 使用“foreach 容器”遍历每个订单，我在其中放置了一个数据流任务，该任务select top 1 * where CustomerID = ? and LevelGrantedOn < ? order by LevelGrantedOn desc从 CRM 数据库中提取数据（两个参数都来自第一步），派生一些列并将输出写入另一个表。

订单数据库中有两万多条记录，这意味着数据流任务将被执行两万多次。CRM 数据库也将被查询超过两万次。做这些需要一个多小时。

我可以利用一些内置功能来加速这些（或以“智能”方式进行）吗？而且，在 ETL 和/或 SSIS 的上下文中，一个小时很长吗？

sql-server ssis

2 个回答

  1. Best Answer

    您可以在不使用 Foreach 枚举器的情况下实现这一点，只需使用一个数据流任务即可实现。

    构建包

    首先，在控制Flow中添加一个Data Flow Task

    1.OLEDB 资源

    在数据流任务中添加一个OLEDB Source从订单表中读取的命令（与Execute SQL Task （问题的第一步）中使用的命令相同

    Select * FROM Order

    还要添加第二个OLEDB Source从客户表中读取的内容：

    select * FROM CRM

    2.排序

    在每个 OLEDB Source 之后添加一个源组件：

    1. 您必须选择的第一个（订单表）CustomerID和OrderID用于排序的列，并选择排序类型为升序
    2. 第二个（客户表）你必须选择CustomerID （排序类型=升序）和LevelGrantedOn （排序类型=降序）列进行排序

    3.合并加入

    添加一个 merge Join 组件来连接两个排序的输出。并CustomerID从两个输出中选择列作为 Join Key 并从两个表中选择您需要的输出列

    4. 有条件拆分

    merge Join 后添加条件拆分以仅过滤与以下表达式匹配的行

    [LevelGrantedOn] < [orderdate]

    5.脚本组件

    我们必须使用的最后一个组件是一个脚本组件，它只获取每个 customerID 的第一行（因为两个源都排序良好，只是抓取第一行是类似的Select top 1 ... ORDER BY LevelGrantedOn desc）

    在脚本组件OutFlag中添加类型的输出列DT_BOOL并使用以下脚本：

    在此处输入图像描述

    当 customerID 第一次出现时，此脚本会将 OutFlag 设置为 True（类似于TOP 1）

        Public Class ScriptMain
        Inherits UserComponent


        Dim lstCustomerID As New System.Collections.Generic.List(Of Integer)
        Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

            If lstCustomerID.Contains(Row.ID) Then

                Row.OutFlag = False
            Else

                lstCustomerID.Add(Row.ID)
                Row.OutFlag = True


            End If



        End Sub

    End Class

    6. 有条件拆分

    添加第二个条件拆分以过滤前 1 行：

    [OutFlag] == True

    7.OLEDB目的地

    将 Conditional Split Output 连接到 OLEDB Destination 顶部并映射列。

    数据流任务必须看起来像

    在此处输入图像描述

    • 3

  2. 考虑使用 SQL Server 的“Index with included columns”功能，它可以在数据收集过程中加快查询性能。

    • -1

相关问题