SQL Server のチューニングについてまとめてみる - その6 - ( CONVERT_IMPLICIT、暗黙の型変換の怖さを知ろう )
おお、錦織選手決勝進出しましたね!おめでとうございます♪ 優勝が見えてきました。
【速報】錦織 日本人初で自身初のGS決勝進出、世界ランク1位のジョコビッチ撃破<男子テニス> (tennis365.net) - Yahoo!ニュース
いやーよかった。それでは、本題に戻りましょう。開発が佳境に入ってくると、『もうデータが取れればいいや!』と勢いだけで SQL を書いてしまいがちです。そのような過程でやってしまいがちのことの一つが『暗黙の型変換』だと思います。場合によっては、かなり恐ろしい事象も発生することがあります。
早速、暗黙の型変換の例をみてみましょう。今回は、AdventureWorks2012 の HumanResources.Employee テーブルを使用します。HumanResources.Employee のテーブル構成は下図の通りです。今回は、赤枠の列を Where 句の条件とします。
それでは、まず暗黙の型変換を発生させる下記のクエリを SQL Server Management Studio で実行してみましょう。
USE AdventureWorks2012
SET STATISTICS PROFILE ON
SELECT *
FROM HumanResources.Employee
WHERE NationalIDNumber = 295847284
上図から、Index Scan 部分を参照してみましょう。暗黙の型変換 ( CONVERT_IMPLICIT ) が発生しているのがわかります。これは大問題です。左辺が int 型に変換されています。これによりインデックスが有効にならないため、Index Scan が発生しています。( 左辺に関数とか使ったらダメなのは基本ですよね? )
|--Index Scan(OBJECT:([AdventureWorks2012].[HumanResources].[Employee].[AK_Employee_NationalIDNumber]), WHERE:(CONVERT_IMPLICIT(int,[AdventureWorks2012].[HumanResources].[Employee].[NationalIDNumber],0)=(295847284)))
IndexScan を発生させないためには、暗黙の型変換を発生させないように、正しい型で条件を設定する必要があります。nvarchar の列に対して、正しい型で条件を指定しました。下記のクエリを SQL Server Management Studio で実行してみましょう。
USE AdventureWorks2012
SET STATISTICS PROFILE ON
SELECT *
FROM HumanResources.Employee
WHERE NationalIDNumber = N'295847284'
初めに実行したクエリは、Scan でしたが、今回は Seek になっています。プラン内容を参照してみましょう。いい感じになってますね。
|--Index Seek(OBJECT:([AdventureWorks2012].[HumanResources].[Employee].[AK_Employee_NationalIDNumber]), SEEK:([AdventureWorks2012].[HumanResources].[Employee].[NationalIDNumber]=[@1]) ORDERED FORWARD)
上記の内容から 暗黙の型変換の怖さが実感できたかと思います。暗黙の型変換のすべてがここまで重症化するとは限りませんが、ゼロにするのが理想だと思います。たとえば、 下記のクエリを実行すると 暗黙の型変換が発生します。
USE AdventureWorks2012
SET STATISTICS PROFILE ON
SELECT *
FROM HumanResources.Employee
WHERE NationalIDNumber = '295847284'
プランを参照してみましょう。条件を UNICODE で指定しなかったので、暗黙の型変換が発生しています。この場合は、右辺が変換されているので、インデックスはうまく使用されています。
|--Index Seek(OBJECT:([AdventureWorks2012].[HumanResources].[Employee].[AK_Employee_NationalIDNumber]), SEEK:([AdventureWorks2012].[HumanResources].[Employee].[NationalIDNumber]=CONVERT_IMPLICIT(nvarchar(4000),[@1],0)) ORDERED FORWARD)
最後に 暗黙の型変換が発生しているクエリを抽出してみましょう。前回の記事で、クエリプランの XML を XQuery で処理しました。今回も同じような方法で、暗黙の型変換が発生しているクエリを特定したいと思います。
SQL Server のチューニングについてまとめてみる - その5 - ( クエリプランから不足インデックス情報を取得する ) - 都内で働くSEの技術的なひとりごと
下記のクエリを SQL Sever Management Studio で実行してみましょう。
WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp)
SELECT [データベース名] = qp.query_plan.value('(//sp:ColumnReference/@Database)[1]', 'nvarchar(100)'),
[スキーマ名] = qp.query_plan.value('(//sp:ColumnReference/@Schema)[1]', 'nvarchar(100)'),
[テーブル名] = qp.query_plan.value('(//sp:ColumnReference/@Table)[1]', 'nvarchar(100)'),
[SQL文] = SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN
DATALENGTH(st.text)
ELSE
qs.statement_end_offset
END -
qs.statement_start_offset)/2
) + 1),
[暗黙知変換情報] = qp.query_plan.query('//sp:ScalarOperator[contains(@ScalarString, ''CONVERT_IMPLICIT'')]'),
[クエリプラン詳細] = qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_query_plan(plan_handle) qp
CROSS APPLY sys.dm_exec_sql_text(sql_handle) st
WHERE qp.query_plan.exist('//sp:ScalarOperator[contains(@ScalarString, ''CONVERT_IMPLICIT'')]') = 1
赤枠の部分をクリックします。
下記の XML が表示されます。暗黙の型変換が発生しているのが確認できます。
<sp:ScalarOperator xmlns:sp="http://schemas.microsoft.com/sqlserver/2004/07/showplan" ScalarString="CONVERT_IMPLICIT(int,[AdventureWorks2012].[HumanResources].[Employee].[NationalIDNumber],0)=(295847284)">
<sp:Compare CompareOp="EQ">
<sp:ScalarOperator>
<sp:Convert DataType="int" Style="0" Implicit="1">
<sp:ScalarOperator>
<sp:Identifier>
<sp:ColumnReference Database="[AdventureWorks2012]" Schema="[HumanResources]" Table="[Employee]" Column="NationalIDNumber" />
</sp:Identifier>
</sp:ScalarOperator>
</sp:Convert>
</sp:ScalarOperator>
<sp:ScalarOperator>
<sp:Const ConstValue="(295847284)" />
</sp:ScalarOperator>
</sp:Compare>
</sp:ScalarOperator>
クエリでまめに実行プランをチェックして、暗黙の型変換をなくしていきましょう♪
※↓この本、プライム会員だったら 0円ですね。早速、呼んでみよう♪