首页 > 代码库 > Sql Server中三种字符串合并方法的性能比较

Sql Server中三种字符串合并方法的性能比较

   最近正在处理一个合并字符吕的存储过程,在一个测试系统的开发中,要使用到字符串合并功能,直接在Sql中做。
        示例:
        有表內容﹕
        名称  內容
         1     abc
        1      aaa
        1      dddd
        2      1223
        2       fkdjfd
           --------------------------------
        结果﹕
        1   abc,aaa,dddd
        2   1223,fkdjfd
        要求用一条SQL语句实现﹐如﹕select sum(內容) from table group by 名称

        --该问题,一共使用了三种方法,并分别测试了一下这三种方法的各自的性能
        1: 创建处理函数
        2 :  sql 2005及以上版本中的新的解决方法,FOR XML
        3 :  使用临时表实现字符串合并处理的示例

说明:以下测试是以本人机器的硬件配置为准,根据硬件配置的不同,结果可能不同。


1: 创建处理函数
       说明:sql 全系列版本

CREATE FUNCTION dbo.f_strHeBin(@id int)
RETURNS varchar(8000)
AS
BEGIN
    DECLARE @r varchar(8000)
    SET @r = ‘‘
    SELECT @r = @r + ‘,‘ + value
    FROM tb
    WHERE id=@id
    RETURN STUFF(@r, 1, 1, ‘‘)
END
GO
-- 调用函数

SELECt id, values=dbo.f_strHeBin(id)
FROM tb
GROUP BY id

以上方式得到的是根据ID合并的所有记录,如果要得到相应的单一ID的记录,则还需要添加一条语句:
假设:以上结果入到临时表3t3中:
SELECT id,max(values) as values FROM #t3 GROUP BY id

go

分析结果如下:
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 XML

select id,stuff(
(select ‘-‘ + convert(varchar(4),value)
from tb
where id=A.id
order by id
for xml path(‘‘)
),1,1,‘‘) as values 
from tb A
group by   id

go

分析结果如下:

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 tb
ORDER BY id

DECLARE @col1 varchar(5),@col2 varchar(8000)
UPDATE #t2 SET
    @col2=CASE WHEN @col1=id THEN @col2+‘-‘+values ELSE values END,
    @col1=zo3,
    qs=@col2


SELECT id,max(values) values FROM #t2 group by id

drop table #t2


go

分析结果如下:


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 执行时间:
   CPU 时间 = 0 毫秒,占用时间 = 1 毫秒。

SQL Server 执行时间:
   CPU 时间 = 0 毫秒,占用时间 = 0 毫秒。


   总计耗时:769ms+62ms+15ms=846ms,总费时1秒钟不到,当数据记录达到20000条记录时,性能损耗也不太严重,在6-10秒左右。能够接受。

 

来自dotnet菜园

http://www.cnblogs.com/chillsrc/archive/2011/03/02/1969010.html

Sql Server中三种字符串合并方法的性能比较