Sql Server中三種字串合并方法的效能比較

標籤：blog   http   io   ar   使用   sp   for   strong   資料   

   最近正在處理一個合并字元呂的預存程序，在一個測試系統的開發中，要使用到字串合并功能，直接在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 [email protected]
    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,
    [email protected]


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中三種字串合并方法的效能比較