暫存資料表vs.表變數以及它們對SQLServer效能的影響

暫存資料表vs.表變數以及它們對SQLServer效能的影響

--王成輝翻譯整理，轉貼請註明出自微軟BI開拓者http://www.windbi.com/
--原帖地址


在暫存資料表
create table #T (…)
和表變數
declare @T table (…)
之間主要有3個理論上的不同。

第一個不同使交易記錄不會記錄表變數。因此，它們脫離了事務機制的範圍，從下面的例子可顯而易見：

create table #T (s varchar(128))
declare @T table (s varchar(128))
insert into #T select 'old value #'
insert into @T select 'old value @'
begin transaction
    update #T set s='new value #'
    update @T set s='new value @'
rollback transaction
select * from #T
select * from @T

s
---------------
old value #

s
---------------
new value @

在聲明暫存資料表#T和表變數@T之後，給它們分配一個相同的值為old value字串。然後，開始一個事務去更新它們。此時，它們都將有新的相同的值new value字串。但當交易回復時，正如你所看到的，表變數@T保留了這個新值而沒有返回old value字串。這是因為即使表變數在事務內被更新了，它本身不是事務的一部分。

第二個主要的不同是任何一個使用暫存資料表的預存程序都不會被先行編譯，然而使用表變數的預存程序的執行計畫可以預先靜態編譯。先行編譯一個指令碼的主要好處在於加快了執行的速度。這個好處對於長的預存程序更加顯著，因為對它來說重新編譯代價太高。

最後，表變數僅存在於那些變數能存在的相同範圍內。和暫存資料表相反，它們在內部預存程序和exec（string）語句裡是不可見的。它們也不能在insert/exec語句裡使用。

效能比較

首先，準備一個有100萬記錄的測試表：

create table NUM (n int primary key, s varchar(128))
GO
set nocount on
declare @n int
set @n=1000000
while @n>0 begin
    insert into NUM
          select @n,'Value: '+convert(varchar,@n)
    set @n=@n-1
    end
GO

準備測試預存程序T1：

create procedure T1
    @total int
as
    create table #T (n int, s varchar(128))
    insert into #T select n,s from NUM
          where n%100>0 and n<=@total
    declare @res varchar(128)
    select @res=max(s) from NUM
          where n<=@total and
              not exists(select * from #T
              where #T.n=NUM.n)
GO

使用參數從10，100，1000，10000，100000到1000000不等來調用，它複製給定數量的記錄到暫存資料表（一些另外，它跳過那些能被100整除的數值），然後找到缺失記錄的最大值。當然，記錄越多，執行的時間就越長：
為了測量正好的執行時間，使用下面的代碼：

declare @t1 datetime, @n int

set @t1=getdate()
set @n=100 – (**)
while @n>0 begin
    exec T1 1000 – (*)
    set @n=@n-1 end
select datediff(ms,@t1,getdate())
GO

（*）表示程式裡邊的參數從10到1000000不等。
（**）表示如果執行時間太短，就重複相同的迴圈10到100次不等。

多次運行代碼以獲得執行的結果。

該結果在下面的表1裡能找到。

下面試著給暫存資料表添加一個主鍵來提升預存程序的效能：

create procedure T2

    @total int
as
    create table #T (n int primary key, s varchar(128))
    insert into #T select n,s from NUM
          where n%100>0 and n<=@total
    declare @res varchar(128)
    select @res=max(s) from NUM
          where n<=@total and
              not exists(select * from #T
              where #T.n=NUM.n)
GO

然後，建立第三個。此時有叢集索引，它會工作得更好。但是是在插入資料到暫存資料表之後建立的索引——通常，這樣會更好：

create procedure T3
    @total int
as
    create table #T (n int, s varchar(128))
    insert into #T select n,s from NUM
          where n%100>0 and n<=@total
    create clustered index Tind on #T (n)
    declare @res varchar(128)
    select @res=max(s) from NUM
          where n<=@total and
              not exists(select * from #T
              where #T.n=NUM.n)
GO

令人驚奇！大資料量花費的時間很長；僅僅添加10條記錄就花費了13毫秒。這個問題在於建立索引語句強迫SQLServer去重新編譯預存程序，顯著的降低了執行效率。

現在試著使用表變數來完成相同的事情：

create procedure V1
    @total int
as
    declare @V table (n int, s varchar(128))
    insert into @V select n,s from NUM
          where n%100>0 and n<=@total
    declare @res varchar(128)
    select @res=max(s) from NUM
          where n<=@total and
              not exists(select * from @V V
              where V.n=NUM.n)
GO

使我們驚奇的是，該版本不是明顯的比用暫存資料表的快。這是由於在預存程序開頭建立表#T語句時進行了特別最佳化的緣故。對整個範圍內的值，V1和T1工作得一樣好。

下面試試有主鍵的情形：

create procedure V2
    @total int
as
    declare @V table (n int primary key, s varchar(128))
    insert into @V select n,s from NUM
          where n%100>0 and n<=@total
    declare @res varchar(128)
    select @res=max(s) from NUM
          where n<=@total and
              not exists(select * from @V V
              where V.n=NUM.n)
GO

這個結果很快，但T2超過了該版本。

Records	T1	T2	T3	V1	V2
10	0.7	1	13.5	0.6	0.8
100	1.2	1.7	14.2	1.2	1.3
1000	7.1	5.5	27	7	5.3
10000	72	57	82	71	48
100000	883	480	580	840	510
1000000	45056	6090	15220	20240	12010

表1：使用SQLServer2000，時間單位毫秒

但真正使我們震驚的是在SQLServer2005上的情形：

N	T1	T2	T3	V1	V2
10	0.5	0.5	5.3	0.2	0.2
100	2	1.2	6.4	61.8	2.5
1000	9.3	8.5	13.5	168	140
10000	67.4	79.2	71.3	17133	13910
100000	700	794	659	Too long!	Too long!
1000000	10556	8673	6440	Too long!	Too long!

表2：使用SQLServer2005（時間單位毫秒）

有時，SQL2005比SQL2000快（上面標記為綠色的部分）。但大多數情況下，特別是在資料量巨大時，預存程序使用表變數花費了更長的時間（紅色部分）。在4種情形下，我甚至放棄了等待。

結論

• 在什麼時候和什麼地方使用暫存資料表或表變數沒有一個普遍的規則。試著都測試測試它們。
• 在你的測試裡，少量的記錄和大量的資料集都要進行測試。
• 當在你的預存程序裡使用了複雜的邏輯的時候要小心遷移到SQL2005。相同的代碼在SQLServer2005上可能運行要慢10到100倍