暫存資料表VS表變數：因地制宜，合理使用(1)

暫存資料表VS表變數：因地制宜，合理使用(1)

一直以來大家對暫存資料表與表變數的孰優孰劣爭論頗多,一些技術群裡的朋友甚至認為表變數幾乎一無是處,比如無統計資訊,不支援事務等等.但事實並非如此.這裡我就暫存資料表與表變數做個對比,對於大多數人不理解或是有歧義的地方進行詳細說明.

注:這裡只討論一般暫存資料表,對全域暫存資料表不做闡述.

生命週期

暫存資料表:會話中,proc中,或使用顯式drop

表變數:batch中

這裡用簡單的code說明表變數範圍

 

  
DECLARE @t TABLE(i int) ----定義表變數@t  
  
 
  
SELECT *FROM @t        -----訪問OK  
  
 
  
insert into @t select 1 -----插入資料OK  
  
 
  
select * from  @t      -------訪問OK  
  
go                     -------結束批處理  
  
select * from @t       -------不在範圍出錯 
 

注意:雖然說sqlserver在定義表變數完成前不允許你使用定義的變數.但注意下面情況仍然可正常運行!

 

  
if 'a'='b' 
  
begin 
  
DECLARE @t TABLE(i int)  
  
end 
  
SELECT *FROM @t        -----仍然可以訪問！  
 

日誌機制

暫存資料表與表變數都會記錄在tempdb中記錄日誌

不同的是暫存資料表的活動紀錄在事務完成前是不能截斷的.

這裡應注意的是由於表變數不支援truncate,所以完全清Null 物件結果集時暫存資料表有明顯優勢,而表變數只能delete

事務支援

暫存資料表:支援

表變數:不支援

我們通過簡單的執行個體加以說明

 

  
create table #t (i int)  
  
declare @t table(i int)  
  
 
  
BEGIN TRAN ttt  
  
insert into #t select 1  
  
insert into @t select 1  
  
SELECT * FROM #t  ------returns 1 rows  
  
SELECT * FROM @t  ------returns 1 rows  
  
ROLLBACK tran ttt  
  
 
  
SELECT * FROM #t    -------no rows  
  
SELECT * FROM @t    -------still 1 rows  
  
drop table #t       ----no use drop @t in session 
 

鎖機制(select)

暫存資料表 會對相關對象加IS(意圖共用)鎖

表變數 會對相關對象加SCH-S(架構共用)鎖(相當於加了nolock hint)

可以看出雖說鎖的影響範圍不同,但由於範圍都只是會話或是batch中,暫存資料表的IS鎖雖說相容性不如表變數的SCH-S但絕大多數情況基本無影響.

感興趣的朋友可以用TF1200測試

索引支援

暫存資料表 支援

表變數 條件支援(僅SQL2014)

沒錯,在sql2014中你可以在建立表的同時建立索引 圖1-1

注:在sql2014之前表變數只支援建立一個預設的唯一性限制式

cod

 

  
DECLARE @t TABLE   
  
(  
  
col1 int index inx_1 CLUSTERED,   
  
col2 int  index index_2 NONCLUSTERED,  
  
       index index_3 NONCLUSTERED(col1,col2)  
  
) 
 

圖1-1

 

  
CREATE FUNCTION TVP_Customers (@cust nvarchar(10))  
  
RETURNS TABLE 
  
AS 
  
 RETURN 
  
 (SELECT RowNum, CustomerID, OrderDate, ShipCountry  
  
 FROM BigOrders  
  
 WHERE CustomerID = @cust);  
  
GO  
  
CREATE FUNCTION TVF_Customers (@cust nvarchar(10))  
  
RETURNS @T TABLE (RowNum int, CustomerID nchar(10), OrderDate date,  
  
 ShipCountry nvarchar(30))  
  
AS 
  
BEGIN 
  
 INSERT INTO @T  
  
  SELECT RowNum, CustomerID, OrderDate, ShipCountry  
  
  FROM BigOrders  
  
  WHERE CustomerID = @cust  
  
  RETURN 
  
END;  
  
 
  
DBCC FREEPROCCACHE  
  
GO  
  
SELECT * FROM TVF_Customers('CENTC');  
  
GO  
  
SELECT * FROM TVP_Customers('CENTC');  
  
GO  
  
SELECT * FROM TVF_Customers('SAVEA');  
  
GO  
  
SELECT * FROM TVP_Customers('SAVEA');  
  
GO  
  
 
  
select b.text,a.execution_count,a.* from sys.dm_exec_query_stats a  
  
cross apply sys.dm_exec_sql_text(a.sql_handle) b  
  
where b.text like '%_Customers%' 
 

圖1-2

其它方面

表變數不支援select into，alter，truncate，dbcc等

表變數不支援table hint 如(force seek)

執行計畫預估

我想這裡可能是引起使用何種方式爭論比較突出的地方,由於表變數沒有統計資訊,無法添加索引等使得大家對其在執行計畫中的效能表現嗤之以鼻,但實際情況呢?我們需要深入分析.

關於暫存資料表的預估這裡我就不做介紹了,主要對錶變數的預估做詳細闡述.

表變數在sql2000引入的一個原因就是為了在一些執行過程中減少重編譯.以獲得更好的效能.當然帶來好處的同時也會帶來一定弊端.由於其不涉及重編譯,最佳化器其實並不知道表變數中的具體行數,此時他採取了保守的預估方式:預估行數為1行.2-1

Code

 

  
declare @t table (i int)  
  
select * from @t-----此時0行預估行數為1行  
  
insert into @t select 1  
  
select * from @t-----此時1行,預估行數仍為1行  
  
insert into @t values (2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(14),(15),(16),(17),(18),(19),(20)  
  
select * from @t ----此時19行,預估行數仍為1行  
  
 
  
--....無論實際@t中有多少行,由於沒有重編譯,預估均為1行 
 

圖2-1

所以當我們加上重編譯的的操作,此時最佳化器就知道了表變數的具體行數.2-2

Code

 

  
declare @t table (i int)  
  
select * from @t option(recompile)-----此時0行預估行數為1行  
  
insert into @t select 1  
  
select * from @t  option(recompile)-----此時1行,預估行數為1行  
  
insert into @t values (2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(14),(15),(16),(17),(18),(19),(20)  
  
select * from @t  option(recompile)----此時19行,預估行數為19行  
  
--....當加入重編譯hint時,最佳化器就知道的表變數的行數. 
 

圖2-2

至此,我們可以看到最佳化器知道了表變數中的行數.這樣在表變數掃描的過程中,尤其針對資料量較大的情形,不會因為預估總是1而引起一些問題.

如果你剛知道這裡的預估原理,現有的代碼都加上重編譯那工作量可想而知了..這裡介紹一個新的跟蹤標記,Trace Flag 2453.

TF2453可以一定程度上替代重編譯Hint,但只是在非簡單計劃trivial plans）的情形下

注:TF2453隻在sql2012 SP2和SQL2014中的補丁中起作用