Compartilhar via

DO’s&DONT’s #10: やらない方がいいこと - クエリの条件句で変数を参照する

  • Artigo

神谷 雅紀
Escalation Engineer

 

クエリの WHERE 句や JOIN ON 句など、クエリのフィルタ条件や結合条件で変数を参照すると、クエリのパフォーマンスが悪化する (実行時間が長くなる) 可能性が高まります。

 

なぜ？

 

必ずしも最適ではない実行プランが生成される可能性

SQL Server は、クライアントから送信されてくるバッチ (クライアントから送信されてくるひとつ以上のステートメントの塊) をコンパイルし、バッチ内の各ステートメントの実行プランを生成し、その後、そのバッチを実行します。実行プランの生成過程であるクエリの最適化 (optimization) では、クエリの検索条件や結合条件などを参照し、最も低コストであると考えられる実行プランを選択します。クエリの検索条件や結合条件は、実行プランの選択に大きな影響を与えます。しかし、変数は、最適化の段階では未知の値です。

以下の例を見てみます。

declare @x nvarchar(10)
set @x = N’ABC’
select * from tab where col1 = @x
go

このバッチは、declare, set, select の 3 ステートメントから構成されています。このバッチの実行が要求されると、まず、これら 3 ステートメントがコンパイルされます。select については、実行プランが生成されます。この実行プランの生成時、@x の値は未知です。なぜならば、@x の値が決定するのは、バッチのコンパイルおよび select の最適化が終わって、実際にこのバッチが実行され、set ステートメントが実行完了した後だからです。select の最適化時には値は確定していません。

つまり、クエリの最適化時には、最適化に必要となる十分な情報が与えられていない状況下で、SQL Server はクエリの実行プランを選択することになります。その結果、生成される実行プランは、必ずしも最適であるとは限らなくなります。

 

最適化にかかる時間が長くなりすぎる、短くなりすぎる可能性

最適化を行うために使用可能な時間は、既に生成された最もコストの低い実行プランのコストをベースに決定されます。

実行プラン生成前のタイムアウト (最初の実行プランが生成されるまでのタイムアウト値) は、SQL Server 内部で定義されている値です。最初に実行プランが生成されると、そのコストを元に最適化のタイムアウト値が再計算されます。その後、さらにコストの低い実行プランが見つかると、その低いコストをベースに再度タイムアウト値が計算されます。最適化中にタイムアウトに達すると最適化は中断され、その時点で最も低いコストの実行プランが最終実行プランとなります。

このような動作を行っているため、例えば、最初に生成された実行プランのコストが過小であると、タイムアウトの時間が非常に短くなり、満足な最適化が行われる前にタイムアウトしてしまう可能性があります。反対に、最初に生成された実行プランのコストが過大であると、クエリによっては、最適化の時間が非常に長くなる可能性があります。ほとんどの場合、タイムアウトする前に最適な実行プランが見つけられるため、最適化がタイムアウトすることは稀です。しかし、最適化に必要な情報を SQL Server に与えることは、良好なパフォーマンスを得るためには重要な事です。

 

以上のような理由から、クエリの条件句で変数を参照することは、クエリを記述する上では避けるべき重要事項のひとつです。

 

どのような対応があるか？

 

変数ではなく、定数もしくはパラメータを使用する方法で対応可能です。

例えば、ストアドプロシージャとして実装されているクエリであれば、変数を参照しているクエリを別のストアドプロシージャに移動し、変数をすべてパラメータ化することで変数を排除することができます。ストアドプロシージャは、指定されたパラメータ値を使用してコンパイルおよび最適化が行われます。コンパイルおよび最適化時、変数は未知の値ですが、パラメータは既知の値です。その結果、ステートメントはパラメータ値を用いて最適化されます。

// 変更前のストアドプロシージャ (select が変数を参照)

create procedure p1
as

declare @x nvarchar(10)
set @x = N’ABC’
select * from tab where col1 = @x
go

// 変更後のストアドプロシージャ (select は別ストアドプロシージャで、変数ではなくパラメータを参照)

create procedure p1
as

declare @x nvarchar(10)
set @x = N’ABC’
exec P1_1 @x
go

create procedure p1_1 (@x nvarchar(10))
as
select * from tab where col1 = @x
go

where col1 = N’ABC’ のような定数は、当然、最適化時に既知の値であり、最適化は、この定数値を用いて行われますので、定数に変更する方法も有効です。ただし、変数を定数化する場合は、DO's&DONT's #2: 絶対にやらなければいけないこと - データ型を一致させる でも触れていますが、定数の表記方法には注意して下さい。

もし、クエリの参照するテーブルのデータ分布が予め分かっており、最適化に有効な変数の値が決定できる場合には、OPTIMIZE FOR ヒントによりその値を指定するという方法もあります。

 

過去の関連トピック

DO's&DONT's #2: 絶対にやらなければいけないこと - データ型を一致させる

DO's&DONT's #3: やらなければいけないこと - 非典型的パラメータ値が存在する場合の再コンパイル (Atypical Parameter Problem の対応)

DO's&DONT's #4: やらない方がいいこと - クエリの 条件句 (WHERE や JOIN ON 等) で参照されている列の加工

DO's&DONT's #6: 絶対にやってはいけないこと – ストアドプロシージャ内でのパラメータ値の変更

Comments

  • Anonymous
    January 02, 2012
    パラメータクエリ全否定ですね。 インジェクション対応の為には全てストアド化しろって いいたいのかな？

  • Anonymous
    January 05, 2012
    「変数」と「パラメータ」は異なります。パラメータ化クエリは、その名前のとおり「パラメータ」を使用しますので、ここで話題にしている「変数」を使用した問題には該当せず、反対に、上で紹介しているように、「変数」を使用しないようにする場合の代替方法のひとつとなります。

  • Anonymous
    January 16, 2012
    変数とパラメータの違い blogs.msdn.com/.../10255393.aspx

  • Anonymous
    April 20, 2016
    この内容は、SQLServer2012以降のバージョンでも該当することなのでしょうか？

    • Anonymous
      July 25, 2016
      SQL Server 2012 以降を含むすべての SQL Server バージョンが該当します。