时间戳

首页 > 解决方案 > 优化 SSIS 数据流任务中使用的 XQuery-heavy SQL 查询

sql - 优化 SSIS 数据流任务中使用的 XQuery-heavy SQL 查询

问题描述

该查询用于每晚将大约 600,000 行数据导入数据仓库中的目标表。每次导入前都会截断目标表。

SELECT -- Around 70 fields from MainTable which contains around 600,000 rows
-- Around 150 fields from around 50 various tables, some quite big
-- Around 35 fields from XQuery derived table queries such as dt_EXTERNAL_CODE1
FROM MainTable
LEFT JOIN -- Around 50 tables
LEFT JOIN
(
SELECT df.ParentID,
ISNULL(df2.XMLValue.value('(Item/*[local-name()="CustomData"]/root/A/Number)[1]', 'float'),0) AS a, 
ISNULL(df2.XMLValue.value('(Item/*[local-name()="CustomData"]/root/B/Number)[1]', 'float'),0) AS b,
ISNULL(df2.XMLValue.value('(Item/*[local-name()="CustomData"]/root/C/Number)[1]', 'float'),0) AS c,
ISNULL(df2.XMLValue.value('(Item/*[local-name()="CustomData"]/root/D/Number)[1]', 'float'),0) AS d,
ISNULL(df2.XMLValue.value('(Item/*[local-name()="CustomData"]/root/E/Number)[1]', 'float'),0) AS e,
ISNULL(df2.XMLValue.value('(Item/*[local-name()="CustomData"]/root/F/Number)[1]', 'float'),0) AS f
FROM DynamicField df
INNER JOIN DynamicField df1 ON df.DynamicFieldID = df1.DynamicFieldID
INNER JOIN DynamicField df2 ON df1.DynamicFieldID = df2.ParentID
WHERE df2.XMLValue.value('(Item/*[local-name()="ExternalCode"])[1]', 'nvarchar(50)') IN('EXTERNAL_CODE1')
) dt_EXTERNAL_CODE1 ON MainTable.DynamicFieldID = dt_EXTERNAL_CODE1.ParentID
LEFT JOIN -- 6 more like the derived table query above, but with some other external code

SSIS 导入作业大约需要 10 小时才能完成。有关如何优化此查询的任何建议？联接不能是内部联接。

标签： sqlsql-serverssisquery-optimizationxquery

解决方案

我只想提一下，这个建议没有考虑可以添加到数据库中 XML 的可能索引。根据您未显示的其他六个查询和许多其他因素，索引 XML 数据可能也是一件好事。我在这里给出的建议实际上只是一般的 X 查询建议，几乎适用于任何 X 查询表达式。

另外值得注意的是，在 SQL 数据库中存储和使用 XML 与关系数据一起进行过滤是一个坏主意，尤其是当您计划使用该数据进行大规模 ETL 解决方案时。正如您已经体验过的那样，这将是一件麻烦事。如果您仍处于可以更改的阶段，我强烈建议您这样做。

除此之外，这里有几个建议：

WHERE df2.XMLValue.value('(Item/*[local-name()="ExternalCode"])[1]', 'nvarchar(50)') IN('EXTERNAL_CODE1')首先，应该重写过滤器表达式以使用exist运算符（Microsoft doc here）。每个微软（这里）：

出于性能原因，不要在谓词中使用 value() 方法与关系值进行比较，而是将 exists() 与 sql:column() 一起使用

其次，我会将新创建的exist表达式移动到连接子句而不是WHERE子句。DynamicField df2当我查看此查询时，优化器可能会在实际执行联接之前将您的过滤器应用于整个表。根据这些连接的基数，这可能对性能非常不利。我想您希望仅对从您的表达式返回的行执行此过滤器FROM DynamicField df INNER JOIN DynamicField df1 ON df.DynamicFieldID = df1.DynamicFieldID。这里的要点是减少将使用任何 XML 过滤的记录数将极大地提高性能。

第三，每次调用value()都将实例化一个新的 XML 阅读器，该阅读器是遍历路径所需的(Item/*[local-name()="CustomData"]/root/D/Number)。减少每个 XML 读取器实例为检索您所需的值而必须执行的工作量将大大提高SELECT性能。如果您正在遍历重复路径（如您的示例），则最好使用附加OUTER APPLY运算符来query调用以将 XML 元素检索root为单独的节点，然后value在 final 的语句中使用该新节点SELECT。像这样的东西：

SELECT 
   df.ParentID
    ,ISNULL(root.RootXmlFrag.value('(root/A/Number)[1]', 'float'),0) AS a
    ...... 
FROM 
    DynamicField df
    INNER JOIN DynamicField df1 ON df.DynamicFieldID = df1.DynamicFieldID
    INNER JOIN DynamicField df2 ON df1.DynamicFieldID = df2.ParentID
    OUTER APPLY df2.XMLValue.query('(Item/*[local-name()="CustomData"]/root)[1]') AS root(RootXmlFrag)

实际的最终query路径表达式可能会有所不同，但使用您不希望像(Item/*[local-name()="CustomData"]/root/D/Number)每个value表达式那样遍历复杂路径的想法，最终肯定会有助于提高性能。

推荐阅读