sql - 为什么合并和转换的组合在串联中如此缓慢？

Question

以下过程将永远持续下去。其目的是将一系列 ID 连接成一个字符串。

declare @validatedIDList nvarchar(max)

SELECT
    @validatedIDList = COALESCE(@validatedIDList + ', ', '') + CAST(si.SelectedID AS nvarchar(50))
FROM 
    dbo.SelectedID si

dbo.SelectedID 是一张只有一个 int 字段的表。它包含大约 60000 行。我尝试将 COALESCE 更改为 ISNULL 但没有显着改善。知道如何让这个请求更快吗？

Answer 1

在包含 1 - 60,000 连续整数的表上运行此命令在我的计算机上只需 5 分钟多一点。

DECLARE @validatedIDList NVARCHAR(max)

SELECT @validatedIDList = COALESCE(@validatedIDList + ', ', '') + 
                          CAST(si.SelectedID AS NVARCHAR(50))
FROM   dbo.SelectedID si

SELECT internal_objects_alloc_page_count,
       internal_objects_dealloc_page_count,
       internal_objects_alloc_page_count - internal_objects_dealloc_page_count
FROM   sys.dm_db_task_space_usage
WHERE  session_id = @@SPID 


SELECT DATALENGTH(@validatedIDList)   

对进程进行分析表明，它花费了大量时间来执行 Blob 操作

为了回答为什么这么慢的问题，nvarchar(max) 变量在 8KB 页面上的 tempdb 中存储为 LOB 数据。

字符串的最终长度为 817,784 字节(大约一百个 8KB 页)。上面查询的结果是

+-----------------------------------+-------------------------------------+-------+
| internal_objects_alloc_page_count | internal_objects_dealloc_page_count |       |
+-----------------------------------+-------------------------------------+-------+
|                           5571528 |                             5571424 |   104 |
+-----------------------------------+-------------------------------------+-------+

显示最终字符串实际上占用了 tempdb 中的 104 页。

将 alloc_page_count 除以 60,000 显示每次分配分配和释放的平均页数为 93。

每个串联操作不仅仅附加到现有的 LOB 数据，而是复制它。随着字符串变长，每次串联的工作量也会相应增加。

XML PATH 方法的实现效率更高，并且具有额外的“好处”，即它实际上被记录为可以工作 ( unlike the method in the question )

文章Concatenating Row Values in Transact-SQL对可用方法进行了很好的总结。