包含XML目标列的包的性能降低

I have done several SSIS packages over the past few months to move data from a legacy database to a SQL Server database. It normally takes 10-20 minutes to process around 5 millions of records depending on the transformation.

在过去几个月里，我已经完成了几个SSIS包，用于将数据从旧数据库移动到SQL Server数据库。根据转换，处理大约5百万条记录通常需要10-20分钟。

The issue I am experiencing with one of my package is a very poor performance because one of the columns in my destination is of the SQL Server XML data type.

我遇到的一个包的问题是性能很差，因为我的目标中的一列是SQL Server XML数据类型。

 Data comes in like this: 5
 A script creates a Unicode string like this: <XmlData><Value>5</Value></XmlData>
 Destination is simply a column with XML data type

This is really slow. Any advice? I did a SQL Trace and notice that in behind the scene SSIS is executing on each row a convert before the insert:

这真的很慢。任何建议？我做了一个SQL跟踪并注意到在场景后面SSIS正在执行每一行转换之前的转换：

 declare @p as xml
 set @p=convert(xml,N'<XmlData><Value>5</Value></XmlData>')

1 个解决方案

#1

Try using a temporary table to store the resulting 5 million records without the XML transformation and then use SQL Server itself to move them from tempDB to the final destination:

尝试使用临时表来存储生成的500万条记录而不进行XML转换，然后使用SQL Server本身将它们从tempDB移动到最终目标：

INSERT INTO final_destination (...)
SELECT cast(N'<XmlData><Value>5</Value></XmlData>' AS XML) AS batch_converted_xml, col1, col2, colX 
FROM   #tempTable

If 5.000.000 turns to be too much data for a single batch, you can do it in smaller batches (100k lines should work like a charm).

如果5.000.000变成一个批次的数据太多，你可以用较小的批次（100k行应该像魅力一样）。

The record captured by the profiler looks like an OleDB transformation with one command per line.

探查器捕获的记录看起来像OleDB转换，每行一个命令。

#1