提高SQL Server中将大型xml文件(~300 MB)转换为关系表的性能

Question

这就是我到目前为止的情况：

--Read xml content into a XML data type variable
DECLARE @FileData XML
SELECT @FileData = CONVERT(XML, BulkColumn)
FROM OPENROWSET(BULK '\\file_path\test.xml', SINGLE_BLOB) AS x
--Read from the XML variable to create Entity-Attribute-Value table
SELECT N1.Id.value('@Id', 'varchar(50)') as Id
    , N1.Id.value('@Name', 'varchar(100)') as Name
    , N2.AttributeLongName.value('@AttributeName', 'varchar(100)') as AttributeName
    , N3.AttributeValue.value('.', 'varchar(MAX)') as AttributeValue
FROM @FileData.nodes('/Data/Entities/Entity') as N1(Id) ---1st lvl Node contains the Entity
cross apply Id.nodes('Attributes/Attribute') as N2(AttributeName) --2nd lvl Node contains AttributeName
cross apply AttributeName.nodes('Values/Value') as N3(AttributeValue) --3rd lvl Node contains AttributeValue

当对一个8 MB的XML文件(~15 s)运行时，这段代码执行得很好。但是，当我尝试在相同结构的300 MB XML文件上运行它时，需要几个小时。

这是否意味着XQuery节点()方法不会线性地缩放或扩展到最坏的范围？

还有别的方法可以提高我的表现吗？

Answer 1

我又看了一遍，可以复制你的问题。尝试将OPTION ( MAXDOP 1 )添加到查询中。在我的测试平台上，一个300 my的文件在1分42秒内运行。在我杀死它之前，这个未暗示的版本在100%的CPU上运行了30分钟。

您也可以看看OPENXML。人们常说，使用大型XML文件会更快，在这种情况下似乎是如此。但是，您应该知道OPENXML存在的已知问题(例如可以占用缓冲池的1/8，是一个老式的COM .dll，您必须调用sp_xml_removedocument等)。一旦您研究了OPENXML的优点和缺点，就可以尝试这样的方法：

DECLARE @FileData XML

SELECT @FileData = BulkColumn
FROM OPENROWSET(BULK 'd:\temp\temp.xml', SINGLE_BLOB) AS x

DECLARE @hDoc int

EXEC sp_xml_preparedocument @hDoc OUTPUT, @FileData 

SELECT *
INTO #tmp
FROM OPENXML( @hDoc, '/Data/Entities/Entity/Attributes/Attribute/Values/Value', 1 ) 
WITH
    (
    Id VARCHAR(50) '../../../../@Id',
    Name VARCHAR(100) '../../../../@Name',
    AttributeName VARCHAR(100)  '../../@AttributeName',
    AttributeValue VARCHAR(MAX) '.'
    )

EXEC sp_xml_removedocument @hDoc

老实说，由于这些问题，我现在尽量避免它；当您刚刚占用了1/8的缓冲池时，一个查询的速度更快有什么意义呢？

最后，最快速和最可伸缩的方法(IMHO)是SSIS。该版本在10秒内运行，与上述方法在我的平台上运行的文件相同。

SSIS导入方法

创建一个包，添加一个数据流任务，添加一个XML，然后添加每个表。我创建了一个与您的文件结构相同的300 eg文件，它在大约10秒钟内加载，例如

显然，您需要额外的时间来查询您导入的表，但我认为这是一种更安全的方法。您甚至可以通过多个包来扩展它。如果您需要更多的帮助，请回发包裹。

Answer 2

我在BLObs上做了很多工作，通常每个都是2MB。如果我执行SQL查询并限制简单的'WHERE ID >x和ID < y‘返回的行数，则查询时间与返回的行数不成线性关系。我在查看系统监视器时发现，使用的内存随着时间的推移而增加，因此我推测，每一行返回的时间都会增加。尝试使用更大的BLObs，我已经耗尽了内存，导致查询中止。