sql-server - 调整查询以解析SQL Server 2014上的XML数据

Question

我在SQL Server 2014数据库上有一张表，该表在VARCHAR(MAX)列（可怜的人CDC）中存储用于记录更改的审核信息。

此数据的格式如下：

<span class="fieldname">Assigned To</span>
   changed from <span class="oldvalue">user1</span>
   to <span class="newvalue">user2</span><br />
<span class="fieldname">Status</span>
   changed from <span class="oldvalue">QA</span>
   to <span class="newvalue">Development</span><br />
<span class="fieldname">Progress</span>
   changed from <span class="oldvalue">Yes</span>
   to <span class="newvalue">No</span><br />
...

我需要解析该信息以便检索转置的数据，以便看起来像这样：

Record    FieldName      OldValue   NewValue
------    ---------      --------   --------
1234      Assigned To    user1      user2
1234      Status         QA         Development
1234      Progress       Yes        No

存储过程尝试通过将数据转换为XML，然后使用XPath检索必要的片段来做到这一点：

;WITH TT AS (
   SELECT TransId,
      CAST('<root><rec>' + REPLACE(REPLACE(TransDescription, 'Ticket reopened... Status', 'Status'), '<br />', '</rec><rec>') + '</rec></root>' AS XML) TransXml
   FROM dbo.Trans
   WHERE TransDate >= '11/1/2016'
      AND (TransDescription LIKE '%Ticket reopened... Status%' OR TransDescription LIKE '%Status%'))
SELECT TransId,
   TransXml,
   FieldName = T.V.value('span[@class="fieldname"][1]', 'varchar(255)'),
   OldValue = NULLIF(T.V.value('span[@class="oldvalue"][1]', 'varchar(255)'), 'nothing'),
   NewValue = NULLIF(T.V.value('span[@class="newvalue"][1]', 'varchar(255)'), 'nothing')
INTO #tmp
FROM TT
   CROSS APPLY TT.TransXml.nodes('root/rec') T(V);

这是执行计划： https://www.brentozar.com/pastetheplan/?id=rJF2GRB7g

相应的IO统计信息：

Table 'Trans'. Scan count 9, logical reads 27429, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 2964994, physical reads 0, read-ahead reads 0, lob logical reads 2991628, lob physical reads 0, lob read-ahead reads 0.

该查询的速度非常慢（该示例仅存储了10天的数据），并且随着数据的增加而变得越来越慢。

我有什么选择来优化此查询？

Answer 1

您真正需要加快速度的是一些xml索引。但是，由于您正在动态创建XML，因此不会发生这种情况。实际上，这与CROSS JOIN大致等效，并且随着时间的流逝将成倍地变慢。

有关详细的讨论以及索引的帮助，请参见cross apply xml query performs exponentially worse as xml document grows。如果要通过XML执行此操作，则确实需要存储XML，以便可以对XML进行索引。