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になりテーブルスキャンが実行されている。
(オリジナル:
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
)
解決策
- multi-statement TVFを別のテーブルとjoinしないで使うつもりなら不正確な推論でも大した問題は無いのでOK
- multi-statement TVFが数行のみを帰す場合もOK
- multi-statement TVFである必要がなくinline TVFに変えられるなら変えた方が正確な推論を使えるのでより良い。
- 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.