Sql Server中三种字符串合并方法.doc

Sql Server中三种字符串合并方法的性能比较最近正在处理一个合并字符吕的存储过程，在一个测试系统的开发中，要使用到字符串合并功能，直接在Sql中做。示例：有表內容名称 內容1 abc1 aaa1 dddd2 12232 fkdjfd-结果1 abc,aaa,dddd2 1223,fkdjfd要求用一条SQL语句实现如select sum(內容) from table group by 名称-该问题,一共使用了三种方法，并分别测试了一下这三种方法的各自的性能1： 创建处理函数2 : sql 2005及以上版本中的新的解决方法,FOR XML3 : 使用临时表实现字符串合并处理的示例说明：以下测试是以本人机器的硬件配置为准，根据硬件配置的不同，结果可能不同。1： 创建处理函数说明：sql 全系列版本CREATE FUNCTION dbo.f_strHeBin(id int)RETURNS varchar(8000)ASBEGIN DECLARE r varchar(8000) SET r = SELECT r = r + , + value FROM tb WHERE id=id RETURN STUFF(r, 1, 1, )ENDGO- 调用函数SELECt id, values=dbo.f_strHeBin(id)FROM tbGROUP BY id以上方式得到的是根据ID合并的所有记录，如果要得到相应的单一ID的记录，则还需要添加一条语句：假设：以上结果入到临时表3t3中:SELECT id,max(values) as values FROM #t3 GROUP BY idgo分析结果如下：SQL Server 分析和编译时间: CPU 时间 = 0 毫秒，占用时间 = 7 毫秒。1-使用sql 全系列版本，自定义合并函数方式SQL Server 执行时间: CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。表 Worktable。扫描计数 1，逻辑读取 4030 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。表 tb。扫描计数 2，逻辑读取 46 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。SQL Server 执行时间:CPU 时间 = 1397254 毫秒，占用时间 = 1463680 毫秒。(1969 行受影响)(218 行受影响)表 Worktable。扫描计数 0，逻辑读取 0 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。表 #t3_000067。扫描计数 1，逻辑读取 16 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。SQL Server 执行时间: CPU 时间 = 0 毫秒，占用时间 = 84 毫秒。SQL Server 执行时间: CPU 时间 = 0 毫秒，占用时间 = 2 毫秒。总计耗时计：24.4分钟，这才1969行记录，这个方法看来在这种情况下不可取。2 : sql 2005及以上版本中的新的解决方法,FOR XMLselect id,stuff(select - + convert(varchar(4),value)from tbwhere id=A.idorder by idfor xml path(),1,1,) as values from tb Agroup by idgo分析结果如下：SQL Server 分析和编译时间: CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。SQL Server 执行时间: CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。表 Worktable。扫描计数 439，逻辑读取 32978 次，物理读取 0 次，预读 0 次，lob 逻辑读取 319 次，lob 物理读取 0 次，lob 预读 0 次。表 tb。扫描计数 4，逻辑读取 92 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。SQL Server 执行时间: CPU 时间 = 1856 毫秒，占用时间 = 1955 毫秒。总计耗时：2秒钟。不过这个方法随着记录数量的增加，性能也随之降低，在数据记录达到20000条的时候，耗时将近2.5分钟。3 : 使用临时表实现字符串合并处理的示例SELECT id ,values=CAST(value as varchar(8000)INTO #t2 FROM tbORDER BY idDECLARE col1 varchar(5),col2 varchar(8000)UPDATE #t2 SET col2=CASE WHEN col1=id THEN col2+-+values ELSE values END, col1=zo3, qs=col2SELECT id,max(values) values FROM #t2 group by iddrop table #t2go分析结果如下：SQL Server 分析和编译时间: CPU 时间 = 7 毫秒，占用时间 = 7 毫秒。SQL Server 执行时间: CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。表 Worktable。扫描计数 0，逻辑读取 0 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。表 tb。扫描计数 2，逻辑读取 46 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。SQL Server 执行时间: CPU 时间 = 734 毫秒，占用时间 = 769 毫秒。(2012 行受影响)表 #t2_000000000065。扫描计数 1，逻辑读取 1677 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。SQL Server 执行时间: CPU 时间 = 62 毫秒，占用时间 = 62 毫秒。(2012 行受影响)表 #t2_000000000065。扫描计数 1，逻辑读取 849 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。SQL Server 执行时间: CPU 时间 = 16 毫秒，占用时间 = 7 毫秒。(218 行受影响)SQL Server 执行时间: