时间戳

首页 > 解决方案 > XML 查询非常慢，当 XML 类型的 .value 方法包含属性过滤器时

sql-server - XML 查询非常慢，当 XML 类型的 .value 方法包含属性过滤器时

问题描述

我有一个 XML 值。它包含相当多的 XML，大约 13 MB。该文件在https://www.profinfo.pl/export/bookseller.xml公开可用 。事实上，它包含一个无效字符，但我在将其传递给 SQL Server 之前删除了所有这些字符。

该文件包含以下 XML 结构：

<?xml version="1.0" encoding="UTF-8"?>
<products>
    <product>
        <productId><![CDATA[108316]]></productId>
        <productCode><![CDATA[PIP-6500:201903]]></productCode>
        <productDateAdd><![CDATA[2019-03-15 14:54:52]]></productDateAdd>
        <productDateUpdate><![CDATA[2019-03-15 15:07:20]]></productDateUpdate>
        <productPrice><![CDATA[67.00]]></productPrice>
        <productNettoPrice><![CDATA[62.04]]></productNettoPrice>
        <productName><![CDATA[Państwo i Prawo - Nr 3/2019 [877]]]></productName>
        <productState><![CDATA[]]></productState>
        <productRateTax><![CDATA[8]]></productRateTax>
        <productBarcode><![CDATA[]]></productBarcode>
        <productUrl><![CDATA[https://www.profinfo.pl/sklep/panstwo-i-prawo-nr-32019-877,7255,r,2019,nr,3.html]]></productUrl>
        <productImages>
            <productImage><![CDATA[https://static.profinfo.pl/image/core_products/2019/3/15/a6e51ae4589402ae9e895c97eb42671e/admin/shop/0031-0980_877.jpg]]></productImage>
            <productImage><![CDATA[https://static.profinfo.pl/image/core_products/2019/3/15/2b0c1bdae2ca13e745d2860428317e74/admin/shop/0031-0980_877.jpg]]></productImage>
            <productImage><![CDATA[https://static.profinfo.pl/image/core_products/2019/3/15/ae609e0b6df358538f385fe4c668ab61/admin/shop/0031-0980_877.jpg]]></productImage>
        </productImages>
        <productCategories>
            <productCategory ID="144"><![CDATA[Historia, teoria i filozofia prawa]]></productCategory>
        </productCategories>
        <productAuthors>
            <productAuthor><![CDATA[Andrzej Wr&oacute;bel]]></productAuthor>
        </productAuthors>
        <productAttributes>
            <productAttribute name="Format"><![CDATA[]]></productAttribute>
            <productAttribute name="Wydanie"><![CDATA[]]></productAttribute>
            <productAttribute name="Medium"><![CDATA[Czasopismo papier]]></productAttribute>
            <productAttribute name="Rodzaj"><![CDATA[]]></productAttribute>
            <productAttribute name="Rok publikacji"><![CDATA[2019]]></productAttribute>
            <productAttribute name="Spis treści"><![CDATA[&lt;p&gt;&lt;strong&gt;TREŚĆ ZESZYTU 3/2019 &bdquo;PAŃSTWA I PRAWA&rdquo;&lt;/strong&gt;&lt;/p&gt;]]></productAttribute>
            <productAttribute name="Wydawnictwo"><![CDATA[Wolters Kluwer Polska]]></productAttribute>
            <productAttribute name="Okładka"><![CDATA[]]></productAttribute>
            <productAttribute name="Liczba stron"><![CDATA[]]></productAttribute>
            <productAttribute name="Opis katalogowy 500"><![CDATA[]]></productAttribute>
            <productAttribute name="Opis"><![CDATA[]]></productAttribute>
            <productAttribute name="Stan prawny"><![CDATA[]]></productAttribute>
            <productAttribute name="ISBN"><![CDATA[]]></productAttribute>
            <productAttribute name="Wersja publikacji"><![CDATA[Czasopismo papier]]></productAttribute>
            <productAttribute name="KodTowaru"><![CDATA[PIP-6500:201903]]></productAttribute>
            <productAttribute name="Dostępność"><![CDATA[]]></productAttribute>
            <productAttribute name="DrukNaŻyczenie"><![CDATA[0]]></productAttribute>
        </productAttributes>
    </product>
    …
</products>

现在，想要在 SQL 中执行以下查询：

SELECT
    product.value('productId[1]', 'VARCHAR(50)') AS id,
    product.value('(productAttributes/productAttribute[@name="Format"])[1]', 'NVARCHAR(255)') AS format
FROM @xml.nodes('/products/product') p(product)

问题是，查询非常慢。我找到了克服这个限制的方法。我可以将此查询提取productAttributes节点重写为单独的 XML 并仅查询这部分：

SELECT
    product.value('productId[1]', 'VARCHAR(50)') AS id,
    attrib.value('productAttribute[@name="Format"][1]', 'NVARCHAR(255)') AS format
FROM @xml.nodes('/products/product') p(product)
    OUTER APPLY (SELECT product.query('productAttributes/productAttribute') AS attrib) a

但我仍然不明白为什么第一个查询的性能如此之低。

为了测试，我将返回数据的大小限制为前 100 行，并尝试比较两个查询的计划。

最可疑的片段是带有 XPath 过滤器的 XML Reader，它返回 3139900 行。

查询计划

这等于 1847（product整个文件中的元素数）乘以 17（productAttribute每个产品中的元素数）乘以 100（顶部返回的行数）。不限制此函数将返回 1847 * 1847 * 17 = 57993953，我认为这是性能低下的原因。它随着 te XML 文件中的产品数量呈二次方增长。

有趣的是，第一个慢得多的查询的复杂性估计为批处理的 31%，第二个为 69%。

有谁知道为什么没有额外.query方法的过滤如此昂贵？

标签： sql-serverxmltsqlxquery

解决方案

请尝试以下方法。

在我的机器上处理您的 13 MB XML 文件只需不到一秒的时间。我还需要删除某种特殊字符以使 XML 文件格式正确。

需要提及的要点：

  • 我们正在将整个 XML 文件加载到具有一行的 SQL Server 表中。所有这些都不是将 XML 文件加载到内存中的 XML 类型的变量中。
  • OUTER APPLY模拟LEFT OUTER JOIN. 我们需要这个，因为 XML 有两个主要的层次结构（一对多）product-to-productAttributes。
  • 在幕后，SQL Server 在处理 XML 时会创建临时表。这就是为什么查询执行计划为每个显示带有 XML 阅读器的表值函数。
  • 最佳实践是在 XQuery方法text()的 XML 元素的每个 XPath 表达式的末尾使用。.value()它是 SQL Server XQuery 的特性。而且我没有看到你正在这样做。

SQL

DECLARE @tbl TABLE(
    ID INT IDENTITY(1, 1) PRIMARY KEY,
    XmlColumn XML
);

INSERT INTO @tbl(XmlColumn)
SELECT * FROM OPENROWSET(BULK N'c:\Temp\bookseller.xml', SINGLE_BLOB) AS x;

SELECT c.value('(productId/text())[1]', 'VARCHAR(10)') AS productId
    , x.value('text()[1]', 'VARCHAR(255)') AS [Format]
FROM @tbl CROSS APPLY XmlColumn.nodes('/products/product') AS t1(c)
    OUTER APPLY t1.c.nodes('productAttributes/productAttribute[@name="Format"]') AS t2(x);

部分输出

+-----------+--------+
| productId | Format |
+-----------+--------+
|    108008 | NULL   |
|    108017 | NULL   |
|    108316 | NULL   |
|    108325 | NULL   |
|    108574 | NULL   |
|    108589 | A5     |
|    108595 | B5     |
+-----------+--------+

推荐阅读