优化SSIS数据流任务中使用的XQuery密集型SQL查询
此查询用于每天晚上将大约60万行导入数据仓库中的目标表。目标表在每次导入之前被截断。
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 codeSSIS导入作业大约需要10个小时才能完成。关于如何优化这个查询，有什么建议吗？连接不能是内部连接。
回答 1
Stack Overflow用户
发布于 2021-09-28 19:52:05
我只想提一下，这个建议没有考虑可能添加到数据库中的XML中的索引。根据您没有显示的其他六个查询和许多其他因素，对XML数据进行索引也可能是一件好事。我在这里给出的建议实际上只是一般的X查询建议，适用于几乎所有的X查询表达式。
还值得注意的是，在SQL数据库以及关系数据中存储和使用XML来进行这样的筛选是个坏主意，特别是当您计划使用该数据执行大规模ETL解决方案时。正如你已经经历过的，这将是一个麻烦。如果你仍处于可以改变这一状况的阶段，我强烈建议你这样做。
撇开这一点不说，这里有几个建议：
首先，应该重写筛选器表达式WHERE df2.XMLValue.value('(Item/*[local-name()="ExternalCode"])[1]', 'nvarchar(50)') IN('EXTERNAL_CODE1')以使用exist操作符(Microsoft 这里)。根据微软(这里)：
出于性能原因，不要在谓词中使用value()方法来与关系值进行比较，而是在sql:column()中使用exist()
其次，我会将新创建的exist表达式移动到join子句，而不是WHERE子句。当我查看此查询时，优化器可能在实际执行联接之前将筛选器应用于整个DynamicField df2表。取决于这些连接的基数，这可能对性能非常不利。我想，您希望只对表达式FROM DynamicField df INNER JOIN DynamicField df1 ON df.DynamicFieldID = df1.DynamicFieldID返回的行执行此筛选。这里的要点是，减少将要使用任何XML筛选的记录数量，将极大地帮助性能。
第三，每次对value()的调用都将实例化遍历路径(Item/*[local-name()="CustomData"]/root/D/Number)所需的新XML读取器。减少XML读取器的每个实例检索SELECT所需的值所需的工作量将大大提高性能。如果您正在遍历一个重复的路径(如您的示例)，那么最好在query调用中使用一个附加的query操作符来检索XML元素root作为一个单独的节点，然后在最终SELECT中的value语句中使用这个新节点。就像这样：
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路径表达式可能会有所不同，但是如果您不想为每个value表达式遍历一个复杂的路径(如(Item/*[local-name()="CustomData"]/root/D/Number) )，那么最终肯定会提高性能。
https://stackoverflow.com/questions/69366825
复制相似问题
腾讯云开发者
