sql server 預存程序中變數表與暫存資料表的分析比較

最近,我有一朋友,對我說他的資料庫中的很多預存程序,執行都是逾時.讓我替他看看是什麼原因.我一看,原來他的預存程序中用了很多的暫存資料表與變數表.於是我跟他說過猶不及.

在預存程序中使用暫存資料表或變數表,使用的好可以提高速度,使用的不好,可能會起到反作用. 然後給了他幾個樣本讓他自己去看,然後針對自己的資料庫進行修改.

那麼表變數一定是在記憶體中的嗎?不一定.

 

通常情況下,表變數中的資料比較少的時候,表變數是存在於記憶體中的。但當表變數保留的資料較多時，記憶體中容納不下，那麼它必須在磁碟上有一個位置來儲存資料。與暫存資料表類似，表變數是在 tempdb 資料庫中建立的。如果有足夠的記憶體，則表變數和暫存資料表都在記憶體（資料緩衝）中建立和處理。

 說明:

     1) CPU-- 事件(sql語句)使用的 CPU 時間（毫秒）。

     2)  Reads--由伺服器代表事件讀取邏輯磁碟的次數。這些讀取運算元包含在語句執行期間讀取表和緩衝區的次數。

     3) Writes--由伺服器代表事件寫入物理磁碟的次數。

 

 

 

 

樣本1.變數表

1) 10000條記錄

declare @t table
(
id nvarchar(50),
supno nvarchar(50),
eta datetime
)
insert  @t

select top 10000 ID,supno,eta from 表

 

--cpu :125    reads :13868    writes: 147

--表 '#286302EC'。掃描計數 0，邏輯讀取 10129 次，物理讀取 0 次，預讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預讀 0 次。

--表 '表'。掃描計數 1，邏輯讀取 955 次，物理讀取 0 次，預讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預讀 0 次。

declare @t table
(
id nvarchar(50),
supno nvarchar(50),
eta datetime
)
insert  @t

select top 1000 ID,supno,eta from 表

 

--    cpu:46    reads:2101     writes:    17   
--表 '#44FF419A'。掃描計數 0，邏輯讀取 1012 次，物理讀取 0 次，預讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預讀 0 次。
--表 '表'。掃描計數 1，邏輯讀取 108 次，物理讀取 0 次，預讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預讀 0 次。

 

 

--樣本2。暫存資料表：

 

create table #t
(
id nvarchar(50),
supno nvarchar(50),
eta datetime
)
end
insert #t
select top 10000 ID,supno,eta
from 表

--cpu :125    reads:13883       writes:148    
--表 '#t00000000005'。掃描計數 0，邏輯讀取 10129 次，物理讀取 0 次，預讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預讀 0 次。
--表 '表'。掃描計數 1，邏輯讀取 955 次，物理讀取 0 次，預讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預讀 0 次。

create table #t
(
id nvarchar(50),
supno nvarchar(50),
eta datetime
)

insert #t
select top 1000 ID,supno,eta
from 表

--cpu: 62    reads: 2095        writes: 17

--表 '#t00000000003'。掃描計數 0，邏輯讀取 1012 次，物理讀取 0 次，預讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預讀 0 次。
--表 '表'。掃描計數 1，邏輯讀取 108 次，物理讀取 0 次，預讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預讀 0 次。

 

 

--樣本3。不建立暫存資料表，直接插入到暫存資料表

select top 10000 ID,supno,eta
into #t
from 表

--cpu:31    reads:1947        writes:83

--表 '表'。掃描計數 1，邏輯讀取 955 次，物理讀取 0 次，預讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預讀 0 次。

select top 1000 ID,supno,eta
into #t
from 表

--cpu: 0    reads: 997        writes:11

--表 '表'。掃描計數 1，邏輯讀取 108 次，物理讀取 0 次，預讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預讀 0 次。

從以上的分析中可以看出,如果使用3)方式,則會少建一個暫存資料表.那麼IO中的讀寫也將減少次數.

1)與2)都會有先建暫存資料表的動作,並進行相應的IO讀取操作.

從sql語句對伺服器的cpu使用上來看,第三種情況cpu使用率也相對較低.

從物理寫入磁碟操作來看,第三種情況的物理寫入次數較少.

 

在什麼情況下使用表變數來代替暫存資料表:

取決於以下三個因素：

•	插入到表中的行數。本人認為最好是小於1000行,具體情況具體分析.
•	從中儲存查詢的重新編譯的次數。
•	查詢類型及其對效能的指數和統計資訊的依賴性。

在某些情況下，可將一個具有暫存資料表的預存程序拆分為多個較小的預存程序，以便在較小的單元上進行重新編譯。

個人建議,當記錄行小於1000行的情況下，應盡量使用表變數，除非資料量非常大(大於1000行)並且需要重複使用表。在這種情況下，可以在暫存資料表上建立索引以提高查詢效能。但是，各種方案可能互不相同。

Microsoft 建議您做一個測試，來驗證表變數對於特定的查詢或預存程序是否比暫存資料表更有效。