我有一个包含 1 个表和大量记录(超过 75000 条)的 Azure SQL 数据库。该表包含一个 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>

如果我想获取项目属性名称为 name5 且值属性字符串为 text5 的所有记录，我将编写如下查询:

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

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

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

最佳答案

我刚刚做了一个小测试。使用 .nodes()，您可以获得 3% 的 yield ……实际上并不多。在我的测试机(只是一台简单的笔记本电脑)中，我在大约 5 秒内得到了 100.000 行的结果。事实上并没有那么糟糕......如果你想要它更快，你必须从 XML 中获取搜索值或者你使用 XML 索引:

测试场景

首先我创建了一个测试表并用 100.000 行填充它。一个随机数(0 到 1000)应该导致每个随机数约 100 行。此数字将作为 XML 中的值放入 varchar col 和。

然后我用 .exist() 和 .nodes() 做一个你需要的调用，第二个有一个小优势，但两者都需要5 到 6 秒。 事实上，我执行了两次调用:第二次以交换顺序并稍微更改了搜索参数并使用“//item”而不是完整路径以避免通过缓存结果或计划误报。

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

现在 - 真正让我吃惊的事情! - 具有完整路径 的.nodes 比以前慢很多(9 秒)，但.exist()缩短至半秒，完整路径甚至缩短至约 0.10 秒

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

下面是复制粘贴和 self 测试的代码

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;

关于sql - 提高 SQL Server 中的 Xquery 性能，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/35575990/