最近,我有一朋友,對我說他的資料庫中的很多儲存過程,執行都是超時.讓我替他看看是什麼原因.我一看,原來他的儲存過程中用了很多的臨時表與變數表.於是我跟他說過猶不及.
在儲存過程中使用臨時表或變數表,使用的好可以提高速度,使用的不好,可能會起到反作用. 然後給了他幾個示例讓他自己去看,然後針對自己的資料庫進行修改.
那麼表變數一定是在記憶體中的嗎?不一定.
通常情況下,表變數中的資料比較少的時候,表變數是存在於記憶體中的。但當表變數保留的資料較多時，記憶體中容納不下，那麼它必須在磁碟上有一個位置來儲存資料。與臨時表類似，表變數是在 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 建議您做一個測試，來驗證表變數對於特定的查詢或儲存過程是否比臨時表更有效。