Azureはじめました

Windows Azureで業務システムを組んでみる日記

multi-statement TVFのパフォーマンスについて

10.hateblo.jp の詳細について見つけた記事を軽く和訳。


この記事はQuery Performance and multi-statement table valued functions - CSS SQL Server Engineers - Site Home - MSDN Blogs の雑な和訳です。

Query Performance and multi-statement table valued functions

最近お客さんのmulti-statementテーブル型関数*1のチューニングを手伝ってるんだけど、TVFを使うときは2-3気をつけなきゃならないことがある。


1つ目に、TVFには以下の2種類がある。

inline TVF
関数が単一のSelect文で出来てる
multi-statement TVF
テーブル型の変数を返し、本文ではこの変数に値を代入してる。

(この記事の最後にinline TVF とmulti-statement TVFのサンプルをつけとく)

2つ目に、multi-statement TVFは一般的に非常に不正確な推論を実行してしまう。*2


inline TVFをViewやパラメータ付きViewのように使う場合、最終的なSQLプランはTVF自身を含まず、代わりに参照オブジェクトをすべて含む。
(意訳:SQLプランキャッシュはTVFをバイパスして内部のSQLそのものをキャッシュする)

しかし、multi-statement TVFをテーブルのように扱う場合、統計情報が使えないためにSQLServerは一般的に不正確な推論を元に実行しなければならない。
もしmulti-statement TVFが数行を返すだけであれば問題はない。
しかし数千行を返したり他のテーブルにjoinする場合非効率なプランが不正確な推論から実行される結果となる。*3

次のデモでは tvf_multi_test() を次のように他のテーブルにJoinしている。

select c.ContactID, c.LastName, c.FirstName, Prod.Name, 
COUNT (*) 'numer of unit' 
from Person.Contact c inner join 
dbo.tvf_multi_Test() tst on c.ContactID = tst.ContactID 
inner join Production.Product prod on tst.ProductID = prod.ProductID 
group by c.ContactID, c.LastName, c.FirstName, Prod.Name

これを実行すると次のように推論がOFFになりテーブルスキャンが実行されている。
f:id:twisted0517:20151021160657p:plain(オリジナル:
http://blogs.msdn.com/cfs-file.ashx/__key/CommunityServer-Blogs-Components-WeblogFiles/00-00-00-73-84-metablogapi/6560.image_5F00_thumb_5F00_5BAB936B.png
)

解決策
  1. multi-statement TVFを別のテーブルとjoinしないで使うつもりなら不正確な推論でも大した問題は無いのでOK
  2. multi-statement TVFが数行のみを帰す場合もOK
  3. multi-statement TVFである必要がなくinline TVFに変えられるなら変えた方が正確な推論を使えるのでより良い。
  4. multi-statement TVFから多くの行が返ることが予想できたり、他のテーブルとjoinするつもりなら、一時テーブルを利用することを検討するほうが良い。*4
デモ
/* 
Purpose: to demonstrate estimate for multi-statement table valued function 
  will have incorrect estimate if large number of rows 
setup: it requires sql 2008 AdventureWorks sample database 
*/

/************************************************************* 
1. creating a TVF to populate from a few other tables 
**************************************************************/ 
use AdventureWorks 
go

if OBJECT_ID ('tvf_multi_Test') is not null 
    drop function tvf_multi_Test 
go

/* 
creating multi-statement TVF 
*/ 
create function tvf_multi_Test() 
returns @SaleDetail table (ContactID int, ProductId int) 
as 
begin 
insert into @SaleDetail 
select ContactID, ProductID from Sales.SalesOrderHeader soh inner join 
Sales.SalesOrderDetail sod on soh.SalesOrderID = sod.SalesOrderID 
return 
end

go

/************************************************************* 
2.  exec plan with the multi-statement TVF 
**************************************************************/ 
set statistics profile on 
set statistics io on 
set statistics time on 
go 
/* 
  the estimate is inaccurate for tvf_multi_Test (always 1 row) 
  the plan is not efficient because it drove 121,317 index seek on Product table 
  and additional 121,317 seeks on contact table 
*/ 
select c.ContactID, c.LastName, c.FirstName, Prod.Name, 
COUNT (*) 'numer of unit' 
from Person.Contact c inner join 
dbo.tvf_multi_Test() tst on c.ContactID = tst.ContactID 
inner join Production.Product prod on tst.ProductID = prod.ProductID 
group by c.ContactID, c.LastName, c.FirstName, Prod.Name

go 
set statistics profile off 
set statistics io off 
set statistics time off

go

/************************************************* 
3. re-write to use inline table valued function 
*************************************************/ 
if OBJECT_ID ('tvf_Inline_Test') is not null 
    drop function tvf_Inline_Test 
go 
create function tvf_Inline_Test() 
returns table 
as 
return select ContactID, ProductID 
    from Sales.SalesOrderHeader soh 
        inner join Sales.SalesOrderDetail sod 
        on soh.SalesOrderID = sod.SalesOrderID

go

/***************************************************** 
4. exec plan for inline TVF 
this will get good plan. 
In fact, you no longer see the table valued function in 
the plan.  It behavies like a view 
******************************************************/ 
set statistics profile on 
set statistics io on 
set statistics time on 
go

select c.ContactID, c.LastName, c.FirstName, Prod.Name, 
COUNT (*) 'numer of unit' 
from Person.Contact c inner join 
dbo.tvf_inline_Test() tst on c.ContactID = tst.ContactID 
inner join Production.Product prod on tst.ProductID = prod.ProductID 
group by c.ContactID, c.LastName, c.FirstName, Prod.Name

go 
set statistics profile off 
set statistics io off 
set statistics time off

*1:table valued functions:TVF

*2:Secondly, multi-statement TVF in general gives very low cardinality estimate.

*3:But if you intend to populate the TVF with thousands of rows and if this TVF is joined with other tables, inefficient plan can result from low cardinality estimate.

*4:If you anticipate large number of rows will result from executing the multi-statement TVF and you will need to join this TVF with other tables, consider putting the results from the TVF to a temp table and then join with the temp table.