SQL Server OPENXML与XQuery性能差异及XQuery优化咨询

问题背景 #

测试SQL Server的OPENXML和XQuery两种XML解析方案时，实测OPENXML性能大幅优于XQuery，与公开资料中XQuery性能更优的结论相悖，需针对现有场景提供XQuery优化方案。 #

业务场景说明 #

数据库表中存储约10000条XML数据，XML结构示例如下：

<Root>
  <Data Code="123" Ref="1">
    <Node>
      <Details Date="2021-06-28" Code="ABC" />
      <Types>
        <Type Type="3" Count="29" />
        <Type Type="5" Count="0" />       
          <TypeDetails Date2="2021-06-30" />
        </Type>
      </Types>
      <Invoice Number="1234" Version="1" />
    </Node>
  </Data>
</Root>

现有代码示例 #

OPENXML实现代码 #

DECLARE name_cursor CURSOR  
FOR  
   SELECT XML   
   FROM  [dbo].[table]
OPEN name_cursor  
DECLARE @xmlVal XML  
DECLARE @idoc int  
FETCH NEXT FROM name_cursor INTO @xmlVal  
  
WHILE (@@FETCH_STATUS = 0)  
BEGIN  
   EXEC sp_xml_preparedocument @idoc OUTPUT, @xmlVal  
  
 INSERT INTO finaltable 
SELECT  Code, Ref, Date, Code, Number,Version, Type, Count,Date2
FROM OPENXML(@iDoc, 'Root/Data/Node/Types/Type')
WITH 
(
Code [varchar](50) '../../../@Code',
Ref [varchar](50) '../../../@Ref',
Date [varchar](50) '../../Details/@Date',
Code [varchar](50) '../../Details/@Code',
Number [varchar](50) '../../Invoice/@Number',
Version [varchar](50) '../../Invoice/@Version',
Type [varchar](50) '@Type',
Count [varchar](50) '@Count',
Date2 [varchar](50) 'TypeDetails/@Date2'

)

   EXEC sp_xml_removedocument @idoc  
   FETCH NEXT FROM name_cursor INTO @xmlVal  
END  
CLOSE name_cursor  
DEALLOCATE name_cursor 

现有XQuery实现代码 #

INSERT INTO finaltable
select 
 c.value('(../../../@Code)[1]','nvarchar(50)') as  Code
 ,c.value('(../../../@Ref)[1]','nvarchar(50)') as  Ref
 ,c.value('(../../Details/@Date)[1]','nvarchar(50)') as  Date
 ,c.value('(../../Details/@Code)[1]','nvarchar(50)') as  Code
      ,c.value('@Type','nvarchar(50)') as  Type
      ,c.value('@Count','nvarchar(50)') as  Count
      ,c.value('(TypeDetails/@Date2)[1]','nvarchar(50)') as  Date2  
 ,c.value('(../../Invoice/@Number)[1]','nvarchar(50)') as  Number
 ,c.value('(../../Invoice/@Version)[1]','nvarchar(50)') as  Version

from [dbo].[table] 
CROSS APPLY XML.nodes('Root/Data/Node/Types/Type') as t(c)

优化方案 #

• 避免反向父节点查询：当前写法大量使用../../../向上遍历父节点的语法是性能差的核心原因，XQuery对向下遍历的优化远好于向上遍历，建议分层拆分CROSS APPLY逐层取节点，优化后的写法参考：

INSERT INTO finaltable
SELECT
    DataNode.value('@Code','nvarchar(50)') AS Code,
    DataNode.value('@Ref','nvarchar(50)') AS Ref,
    DetailNode.value('@Date','nvarchar(50)') AS Date,
    DetailNode.value('@Code','nvarchar(50)') AS DetailCode,
    InvoiceNode.value('@Number','nvarchar(50)') AS Number,
    InvoiceNode.value('@Version','nvarchar(50)') AS Version,
    TypeNode.value('@Type','nvarchar(50)') AS Type,
    TypeNode.value('@Count','nvarchar(50)') AS Count,
    TypeNode.value('(TypeDetails/@Date2)[1]','nvarchar(50)') AS Date2
FROM [dbo].[table]
CROSS APPLY XML.nodes('Root/Data') AS T1(DataNode)
CROSS APPLY DataNode.nodes('Node') AS T2(NodeNode)
CROSS APPLY NodeNode.nodes('Details') AS T3(DetailNode)
CROSS APPLY NodeNode.nodes('Invoice') AS T4(InvoiceNode)
CROSS APPLY NodeNode.nodes('Types/Type') AS T5(TypeNode)

• 为XML列创建XML索引：持久化存储的XML列可先创建主XML索引，再针对高频查询的固定路径创建路径XML索引，解析效率提升幅度非常明显。
• 匹配数据类型：属性值如果是数字、日期类型，不要统一转成nvarchar，使用对应的原生类型（int、date等）可减少类型转换开销。
• 关闭不必要的XML校验：如果不需要DTD验证可关闭对应数据库级XML选项，减少无效校验开销。
• 大XML批量拆分：如果单条XML体积过大，可拆分为小批次处理，避免单次查询内存占用过高导致的性能下降。

内容的提问来源于stack exchange，提问作者DC07