RiCo技術農場

很久已前寫過一篇資料表值函數，當時本還打算寫一篇效能問題，

但之後工作一忙就忘記了，今天在論壇上看到網友詢問 TVF 效能問題，

勾起相關回憶，馬上補寫資料表值函數效能問題。

 

問題:

multi statement table valued function

沒有跟其他的 tables 進行 join 只是單純 select 但效能不是很理想，

若將其內容改為 stored procedure 後, 效能居然效能差異很大，

是哪邊出了問題?

 

Answer:

最大原因應該是MTVF無法使用統計值，

導致查詢最佳化選擇了較差執行計畫所造成效能低落(可能是Full Table Scan)。

 

模擬測試

MTVF必須宣告一個資料表變數，然後必須在Begin End 區段中撰寫相關TSQL，

最後返回結果。

 

CREATE FUNCTIONGetLogMessage(@LoginIdnvarchar(20))
RETURNS@mytblTABLE
(LoginId   nvarchar(20)  NULL,
LogDate DATETIMENULL,
LogMessage  nvarchar(4000)    NULL)
AS
BEGIN 
      
  INSERT@mytblSELECTa.LoginId,a.LogDate,a.LogMessage    
 FROMdbo.AP_LOG a        
 wherea.LoginId=@LoginId     
 RETURN 
 
END

 

執行查詢:

select * from GetLogMessage('ricoisme') 

 

可以看到執行計畫中使用資料表掃描作業和資料表值函數作業，

而且資料表掃描作業估計的資料列數目只有 1，

這是因為MTVF無法使用統計值所以就無法進行優化，整體I/O=136。

 

CREATE proc usp_GetLogMessage(@LoginId nvarchar(20))
as
SELECT a.LoginId,a.LogDate,a.LogMessage      
FROM dbo.AP_LOG a  
where a.LoginId=@LoginId 

執行查詢:

exec usp_GetLogMessage 'ricoisme'

 

呼叫SP可以看到執行計畫中使用索引搜尋作業，

且該索引的統計值=914.986，整體I/O=83。

關於SP參數探測行為可以參考以下兩篇文章

[SQL SERVER][Memo]了解參數探測行為(1)

[SQL SERVER][Memo]了解參數探測行為(2)

 

不使用MVTF也可達到和使用SP相同效能水準

ITVF:

CREATE FUNCTION GetLogMessage2(@LoginId nvarchar(20)) 
RETURNS TABLE 
AS  
RETURN 
SELECT a.LoginId,a.LogDate,a.LogMessage      
FROM dbo.AP_LOG a  
where a.LoginId=@LoginId  

執行查詢:

select * from GetLogMessage2('ricoisme')  

 

相當於帶參數的View，並且使用索引統計值。

 

 

參考

Comparing Inline and Multi-Statement Table-Valued Functions