时间戳

首页 > 解决方案 > SQL将字符串字段转换为XML慢

sql-server - SQL将字符串字段转换为XML慢

问题描述

我在 MS SQL 中有一个表，其中一个字段包含一个像这样表示 XML 的字符串：

        < Root >
           < Value_Tab2 ID = "182" >    
               < Value_Tab3 >
                   < Value > 1219 </ Value > 
              </ Value_Tab3 >
           </ Value_Tab2 >
           < Value_Tab2 ID = "187" >
               < Value_Tab3 >
                   < Value > 3192 </ Value >
               </ Value_Tab3 >
           </ Value_Tab2 >
        </ Root >

我可以将此字段转换字符串查询为 XML，但是对于 50000 行 MyTable1，查询需要 7-8 秒。我使用的查询是这样的：

        SELECT MT1.ID
            ,cast(MT1.StringXml as xml).value('(Root/Value_Tab2/@ID)[1]', 'INT') AS MT1_ID1
            ,cast(MT1.StringXml as xml).value('(Root/Value_Tab2/@ID)[2]', 'INT') AS MT1_ID2
            ,cast(MT1.StringXml as xml).value('(Root/Value_Tab2/Value_Tab3/Value)[1]', 'INT') AS VALUE_ID1
            ,cast(MT1.StringXml as xml).value('(Root/Value_Tab2/Value_Tab3/Value)[2]', 'INT') AS VALUE_ID2
        FROM MyTable1 MT1 with (NOLOCK)
        WHERE MT1.Published = 1

StringXML 是 MyTable1 归档为 nvarchar(MAX); 这是查询字符串字段并转换为 XML 的正确方法吗？

还有其他提高查询性能的方法或解决方法吗？

标签： sql-serverxmltsqlcastingxquery

解决方案

您需要做的就是所谓的 XML 粉碎。

请尝试以下方法。

正如@PanagiotisKanavos 已经指出的那样，CAST 操作只完成一次。所有 XPath 表达式都针对性能进行了优化。

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, Published BIT, StringXml NVARCHAR(MAX));
INSERT INTO @tbl (Published, StringXml) VALUES
(1, N'<Root>
    <Value_Tab2 ID="182">
        <Value_Tab3>
            <Value>1219</Value>
        </Value_Tab3>
    </Value_Tab2>
    <Value_Tab2 ID="187">
        <Value_Tab3>
            <Value>3192</Value>
        </Value_Tab3>
    </Value_Tab2>
</Root>');
-- DDL and sample data population, end

;WITH rs AS
(
    SELECT ID, Published
        , TRY_CAST(StringXml AS XML) AS xmldata
    FROM @tbl
)
SELECT ID, c.value('Value_Tab2[1]/@ID', 'INT') AS MT1_ID1
    , c.value('Value_Tab2[2]/@ID', 'INT') AS MT1_ID2
    , c.value('(Value_Tab2[1]/Value_Tab3/Value/text())[1]', 'INT') AS VALUE_ID1
    , c.value('(Value_Tab2[2]/Value_Tab3/Value/text())[1]', 'INT') AS VALUE_ID2
FROM rs
    CROSS APPLY xmldata.nodes('/Root') AS t(c)
WHERE rs.Published = 1;

输出

+----+---------+---------+-----------+-----------+
| ID | MT1_ID1 | MT1_ID2 | VALUE_ID1 | VALUE_ID2 |
+----+---------+---------+-----------+-----------+
|  1 |     182 |     187 |      1219 |      3192 |
+----+---------+---------+-----------+-----------+

推荐阅读