sql server 預存程序的最佳化.(變數表,暫存資料表的簡單分析) )

 昨日一朋友發來一段sql的預存程序（如下），讓我看看能不能最佳化一下。
insert @T1
  select g_no,co_no,si_no,str_no,sum(ind_qty) as qty
  from instock_detail where  in_id = @id  group by g_no,co_no,si_no,str_no
  
  --?unitstock  -->儲存在變數表中 
  insert @T2
  select a.*  
  from  unitstock a,@T1 b
  where a.g_no =b.g_no  and a.co_no =b.co_no  
  and a.si_no =b.si_no and a.str_no=b.str_no 
 
  delete unitstock
  from @T1 a
  where unitstock.g_no=a.g_no  and unitstock.co_no =a.co_no    
  and unitstock.si_no=a.si_no and unitstock.str_no=a.str_no  
  
  insert unitstock
  select g_no,co_no,si_no,str_no,sum(qty) as qty  from
  ( select  * from @T1   union all select * from @T2 
  ) AA
  group by g_no,co_no,si_no,str_no

今日有空，作了一下變數表，暫存資料表插入資料的效能分析。
1。變數表：

declare @t table
(
id nvarchar(50),
supno nvarchar(50),
eta datetime
)
insert  @t
select top 10000 id,supno,eta from 表

這一句執行sql需時間：16806ms

2。暫存資料表：

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

這一句執行sql需時間：76ms

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

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

這一句執行sql需時間：30ms

通過以上的分析，可以非常清晰的看出那個優，那個劣了。

以上只是簡單的分析了一下。所以在預存程序中盡量合作暫存資料表來儲存臨時資料，不要使用變數表。

 

 

////
 可將函數和變數聲明為 table 類型。table 變數可用於函數、預存程序和批處理中。

儘可能使用表變數而不使用暫存資料表。table 變數有以下優點：

table 變數的行為類似於局部變數，有明確定義的範圍。該範圍為聲明該變數的函數、預存程序或批處理。
在其範圍內，table 變數可像常規表那樣使用。該變數可應用於 SELECT、INSERT、UPDATE 和 DELETE 語句中用到表或表的運算式的地方。但是，table 不能用在下列語句中：

INSERT INTO table_variable EXEC 預存程序。

SELECT select_list INTO table_variable 語句。

在定義 table 變數的函數、預存程序或批處理結束時，自動清除 table 變數。

在預存程序中使用表變數與使用暫存資料表相比，減少了預存程序的重新編譯量。

涉及表變數的事務只在表變數更新期間存在。這樣就減少了表變數對鎖定和記錄資源的需求。
不支援在表變數之間進行賦值操作。另外，由於表變數範圍有限，並且不是持久資料庫的一部分，因而不受交易回復的影響。

////

 

如果資料量不是很大的話(十萬級)，查詢的時間兩者差不多。差個1,2s，對於節約了10，20s來說，不算什麼了。
具體情況具體分析。
我朋友使用以上方式最佳化之後，其他什麼也沒改，比沒有最佳化之前節約了近一半的時間。
@WEBBER
這個具體情況具體分析，變數表的好像就如c#中的局部變數，所以生命週期短，但並不是說一定比暫存資料表優。
暫存資料表需要自己去清除其資源佔用。