提高 SQL Server 中的 Xquery 性能

我有一个 Azure SQL 数据库，其中有 1 个表和大量记录（超过 75,000 条）。该表包含 XML 数据类型的列。此列如下所示：

<error application="application" host="host" type="exception" message="message" ...>
  <serverVariables>
    <item name="name1">
      <value string="text" />
    </item>
    <item name="name2">
      <value string="text2" />
    </item>
    <item name="name3">
      <value string="text3" />
    </item>
    <item name="name4">
      <value string="text4" />
    </item>
    <item name="name5">
      <value string="text5" />
    </item>
    <item name="name6">
      <value string="text6" />
    </item>
    <item name="name7">
      <value string="text7" />
    </item>
  </serverVariables>
</error>

如果我想获取item属性名称为的所有记录name5值属性字符串是text5我会写一个这样的查询：

SELECT *
FROM Table
WHERE XmlColumn.exist('//item[@name[. = "name5"] and value/@string[. = "text5"]]') = 1

这使用 XQuery 并且必须查询整个文档。这也很慢。

我的问题是如何才能使这个查询执行得更快？是否可以在该列上声明 XML 索引？是否还有其他方法可以使 XQuery 执行得更快？

我刚刚做了一个小测试。和.nodes()你可以获得大约 3%...实际上不多。在我的测试机器（只是一台简单的笔记本电脑）中，我在大约 5 秒内得到了 100.000 行的结果。事实上，还不错...如果您想要快速，则必须从 XML 中获取搜索值，或者使用 XML 索引：

测试场景

首先，我创建一个测试表并填充 100.000 行。随机数（0 到 1000）应为每个随机数生成约 100 行。该数字被放入 varchar col 中and作为 XML 中的值。

然后我会像你需要的那样拨打电话.exist()与.nodes()第二个稍有优势，但都需要 5 至 6 秒。 事实上，我执行了两次调用：第二次以交换的顺序进行，并稍微更改了搜索参数，并使用“//item”而不是完整路径，以避免通过缓存的结果或计划出现误报。

然后我创建一个 XML 索引并执行相同的调用

怎么办really确实让我惊讶！ - 这.nodes with 完整路径 is much比以前慢（9秒）但是.exist()缩短至半秒，完整路径甚至缩短至约 0.10 秒

所以我的建议：使用索引并用.exist()

这是复制粘贴和自测试的代码

CREATE TABLE #testTbl(ID INT IDENTITY PRIMARY KEY, SomeData VARCHAR(100),XmlColumn XML);
GO

DECLARE @RndNumber VARCHAR(100)=(SELECT CAST(CAST(RAND()*1000 AS INT) AS VARCHAR(100)));

INSERT INTO #testTbl VALUES('Data_' + @RndNumber,
'<error application="application" host="host" type="exception" message="message" >
  <serverVariables>
    <item name="name1">
      <value string="text" />
    </item>
    <item name="name2">
      <value string="text2" />
    </item>
    <item name="name3">
      <value string="text3" />
    </item>
    <item name="name4">
      <value string="text4" />
    </item>
    <item name="name5">
      <value string="My test ' +  @RndNumber + '" />
    </item>
    <item name="name6">
      <value string="text6" />
    </item>
    <item name="name7">
      <value string="text7" />
    </item>
  </serverVariables>
</error>');

GO 100000

DECLARE @d DATETIME=GETDATE()
SELECT #testTbl.*
FROM #testTbl
CROSS APPLY XmlColumn.nodes('/error/serverVariables/item[@name="name5" and value/@string="My test 600"]') AS a(b);
SELECT CAST(GETDATE()-@d AS TIME) AS NodesFullPath_no_index;
GO

DECLARE @d DATETIME=GETDATE();
SELECT * 
FROM #testTbl
--WHERE XmlColumn.exist('//item[@name[. = "name5"] and value/@string[. = "My test 600"]]') = 1
--The same, just a bit shorter...
WHERE XmlColumn.exist('/error/serverVariables/item[@name="name5" and value/@string="My test 600"]') = 1;
SELECT CAST(GETDATE()-@d AS TIME) AS ExistFullPath_no_index;
GO

DECLARE @d DATETIME=GETDATE();
SELECT * 
FROM #testTbl
--WHERE XmlColumn.exist('//item[@name[. = "name5"] and value/@string[. = "My test 600"]]') = 1
--The same, just a bit shorter...
WHERE XmlColumn.exist('//item[@name="name5" and value/@string="My test 500"]') = 1;
SELECT CAST(GETDATE()-@d AS TIME) AS ExistShortPath_no_index;
GO

DECLARE @d DATETIME=GETDATE()
SELECT #testTbl.*
FROM #testTbl
CROSS APPLY XmlColumn.nodes('//item[@name="name5" and value/@string="My test 500"]') AS a(b);
SELECT CAST(GETDATE()-@d AS TIME) AS NodesShortPath_no_index;
GO

CREATE PRIMARY XML INDEX PXML_test_XmlColum1 ON #testTbl(XmlColumn);
CREATE XML INDEX IXML_test_XmlColumn2 ON #testTbl(XmlColumn) USING XML INDEX PXML_test_XmlColum1 FOR PATH;
GO

DECLARE @d DATETIME=GETDATE()
SELECT #testTbl.*
FROM #testTbl
CROSS APPLY XmlColumn.nodes('/error/serverVariables/item[@name="name5" and value/@string="My test 600"]') AS a(b);
SELECT CAST(GETDATE()-@d AS TIME) AS NodesFullPath_with_index;
GO

DECLARE @d DATETIME=GETDATE();
SELECT * 
FROM #testTbl
--WHERE XmlColumn.exist('//item[@name[. = "name5"] and value/@string[. = "My test 600"]]') = 1
--The same, just a bit shorter...
WHERE XmlColumn.exist('/error/serverVariables/item[@name="name5" and value/@string="My test 600"]') = 1;
SELECT CAST(GETDATE()-@d AS TIME) AS ExistFullPath_with_index;
GO

DECLARE @d DATETIME=GETDATE();
SELECT * 
FROM #testTbl
--WHERE XmlColumn.exist('//item[@name[. = "name5"] and value/@string[. = "My test 600"]]') = 1
--The same, just a bit shorter...
WHERE XmlColumn.exist('//item[@name="name5" and value/@string="My test 500"]') = 1;
SELECT CAST(GETDATE()-@d AS TIME) AS ExistShortPath_with_index;
GO
    
DECLARE @d DATETIME=GETDATE()
SELECT #testTbl.*
FROM #testTbl
CROSS APPLY XmlColumn.nodes('//item[@name="name5" and value/@string="My test 500"]') AS a(b);
SELECT CAST(GETDATE()-@d AS TIME) AS NodesShortPath_with_index;
GO

DROP TABLE #testTbl;