MSSQL 批量字符串到 XML 插入到

Question

目前我们导入一个表Import_File该表具有分隔值的选项列。 我们需要将这些分隔值加载到不同的表中。

目前，我们一次只做一行，这往往更慢，因为行数可以是 100k+

有没有办法加快下面的代码？

Declare @InvId uniqueidentifier
Declare @xml xml
Declare CurrFeatureList Cursor For
Select 
import.InventoryId,
N'<root><r><![CDATA[' + replace( import.OPTIONS ,',',']]></r><r><![CDATA[') + ']]></r></root>'
From                Import_File import with (nolock)
Where
import.options IS NOT NULL
And ISNULL(import.IsFeatureProcessed,0) = 0
And LEN(ISNULL(import.OPTIONS,''))>10
And import.InventoryId Is Not Null
OPEN CurrFeatureList
FETCH NEXT FROM CurrFeatureList
INTO @InvId, @xml
Print 'Inventory Import #10000'
Print GetDate()
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRY
Insert Into Import_File_Feature
(
FeatureId,
InventoryId,
FeatureText,
FeatureGroup,
FeatureCategory,
FeatureIsAvailable,
FeatureIsStandard
)
Select 
NEWID(),
@InvId,
t.value('.','varchar(250)'),
'',
'',
1,
1
From @xml.nodes('//root/r') as a(t)
FETCH NEXT FROM CurrFeatureList
INTO @InvId, @xml
END TRY
BEGIN CATCH
Print 'Error    ' 
Print @InvId 
Print   ERROR_NUMBER() 
Print   ERROR_SEVERITY()
Print   ERROR_STATE()
Print   ERROR_PROCEDURE()
Print   ERROR_LINE()
Print   ERROR_MESSAGE()
FETCH NEXT FROM CurrFeatureList
INTO @InvId, @xml
END CATCH
END
Close CurrFeatureList
Deallocate CurrFeatureList
GO

Answer 1

我见过用于这种事情的游标和TRY/CATCH块的唯一原因是在开发新的ETL过程时识别和分析不良记录。如果这不是您正在执行的操作，则不需要光标，这会减慢您的速度。

让我们回顾一下您正在执行的操作：

1. 您正在将数据拉入光标 (CurrFeatureList) 并将 Import_File.Options 转换为 XML 字段，以便以后可以使用 XML NODES 方法"拆分"字符串。
2. 踢出光标
3. 对于每个库存 ID，您是： a. 将Import_File.选项拆分为多行 b. 将该清单 ID 和关联的行插入到Import_File_Feature c.如果出现错误，请打印它并继续下一条记录

你应该做什么

请注意我如何使用 XML 节点拆分此字符串：

DECLARE @x varchar(100) = 'abc,cde,fff';
SELECT item = xxx.value('(text())[1]', 'varchar(100)')
FROM (VALUES (CAST(('<r>'+REPLACE(@x,',','</r><r>') +'</r>') AS xml))) x(xx)
CROSS APPLY xx.nodes('r') xxx(xxx);

结果

item
-----
abc
cde
fff

选项 1

将初始连接和后续 XML/XML 节点拆分器逻辑合并到一个语句中，然后执行插入：

WITH 
yourData AS
(
Select 
import.InventoryId,
x = N'<r><![CDATA[' + replace( import.OPTIONS ,',',']]></r><r><![CDATA[') + ']]></r>'
From Import_File import with (nolock)
Where
import.options IS NOT NULL
And ISNULL(import.IsFeatureProcessed,0) = 0
And LEN(ISNULL(import.OPTIONS,''))>10
And import.InventoryId Is Not Null
),
split AS
(
SELECT InventoryId, item = i.value('.', 'varchar(8000)')
FROM yourData
CROSS APPLY x.nodes('r') s(i)  
)
Insert Into Import_File_Feature
(
FeatureId,
InventoryId,
FeatureText,
FeatureGroup,
FeatureCategory,
FeatureIsAvailable,
FeatureIsStandard
)
Select 
newid(),
import.InventoryId,
item, -- this is the split out item from import.Options
'',
'',
1,
1
FROM split;

选项 2

获取DelimitSplit8K的副本并使用它来进行拆分。

WITH split AS
(
Select 
import.InventoryId,
import.OPTIONS
From Import_File import with (nolock)
CROSS APPLY dbo.DelimitedSplit8K(import.OPTIONS, ',')
Where
import.options IS NOT NULL
And ISNULL(import.IsFeatureProcessed,0) = 0
And LEN(ISNULL(import.OPTIONS,''))>10
And import.InventoryId Is Not Null
)
Insert Into Import_File_Feature
(
FeatureId,
InventoryId,
FeatureText,
FeatureGroup,
FeatureCategory,
FeatureIsAvailable,
FeatureIsStandard
)
Select 
newid(),
import.InventoryId,
item,
'',
'',
1,
1
FROM split

请注意，由于我没有任何表定义或示例数据，因此无法测试上面的代码。