SQL Server导入1GB XML速度过慢，求优化建议

我有一个1GB大小的XML文件，使用以下SQL代码将其导入SQL Server：

DECLARE @xmlvar XML
SELECT @xmlvar = BulkColumn
FROM OPENROWSET(BULK 'C:\Data\demo.xml', SINGLE_BLOB) x;

WITH XMLNAMESPACES(DEFAULT 'ux:no::ehe:v5:actual:aver',
                            'ux:no:ehe:v5:move' AS ns4,
                            'ux:no:ehe:v5:cat:fill' as ns3,
                            'ux:no:ehe:v5:centre' as ns2)
SELECT
        zs.value(N'(../@versionCode)', 'VARCHAR(100)') as versionCode,
        zs.value(N'(@Start)', 'VARCHAR(50)') as Start_date,
        zs.value(N'(@End)', 'VARCHAR(50)') as End_date
into testtbl
FROM @xmlvar.nodes('/ns4:Dataview1/ns4:Content/ns4:gen') A(zs);

当前该查询已运行超过2小时仍未完成，但用小体积XML文件测试时可正常运行，恳请提供提升加载速度的优化建议。

附XML文件示例：

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<ns4:Dataview1 xmlns="ux:no::ehe:v5:actual:aver" xmlns:ns4="ux:no:ehe:v5:move">
    <ns4:Content versionCode="16000">
        <ns4:gen start="1961-07-01" end="1961-07-01">            
        </ns4:gen>
        <ns4:gen start="2017-09-19">            
        </ns4:gen>
        <ns4:gen start="1961-07-02" end="2016-09-30">           
        </ns4:gen>
        <ns4:gen start="2016-10-01" end="2017-09-18">            
        </ns4:gen>      
    </ns4:Content>
</ns4:Dataview1>

优化建议 #

• 避免将整个XML加载到内存变量：原代码把1GB XML全部加载到@xmlvar变量中，会占用大量内存并引发性能瓶颈。改用OPENROWSET直接结合节点遍历处理，跳过内存变量步骤，同时优化父节点属性的获取逻辑，避免重复DOM遍历：

WITH XMLNAMESPACES(DEFAULT 'ux:no::ehe:v5:actual:aver',
                            'ux:no:ehe:v5:move' AS ns4,
                            'ux:no:ehe:v5:cat:fill' as ns3,
                            'ux:no:ehe:v5:centre' as ns2)
SELECT
    c.value('@versionCode', 'VARCHAR(100)') as versionCode,
    g.value('@start', 'VARCHAR(50)') as Start_date,
    g.value('@end', 'VARCHAR(50)') as End_date
INTO testtbl
FROM OPENROWSET(BULK 'C:\Data\demo.xml', SINGLE_BLOB) x
CROSS APPLY (SELECT CAST(x.BulkColumn AS XML)) AS xml_data(xml_col)
CROSS APPLY xml_data.xml_col.nodes('/ns4:Dataview1/ns4:Content') AS content(c)
CROSS APPLY c.nodes('./ns4:gen') AS gen(g);

• 导入期间禁用自动统计与索引：

  • 导入前执行：SET AUTO_CREATE_STATISTICS OFF;，完成后再执行SET AUTO_CREATE_STATISTICS ON;
  • 不要预先给testtbl创建索引，等数据全部导入后再按需创建，减少插入时的索引维护开销。

• 用SSIS替代纯SQL导入：SQL Server Integration Services（SSIS）的XML源组件采用流式处理，无需加载整个XML到内存，对大体积XML的处理效率远高于纯SQL脚本。创建SSIS包后，直接配置XML源指向目标文件，映射字段到SQL Server表即可。

• 拆分大XML文件：如果无法使用SSIS，可将1GB XML拆分为多个50-100MB的小文件，循环执行导入脚本分批插入，避免单次操作占用过多系统资源。

• 调整SQL Server内存配置：通过sp_configure 'max server memory'增大SQL Server的内存上限，确保有足够内存分配给XML处理，避免因内存不足触发频繁磁盘交换拖慢速度。

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