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

  • Article

神谷 雅紀
SQL Server Escalation Engineer

 

ストアドプロシージャに渡されたパラメータが、ストアドプロシージャ内のクエリで参照されている場合、パラメータ値をストアドプロシージャの中で変更してはいけません。

悪い例

 

CREATE PROCEDURE GetDetails
 (@BranchCode smallint default 0)
AS
BEGIN
-- 支店コードが指定されていない場合は本店
IF @BranchCode = 0 THEN
 SELECT @BranchCode = 100  -- パラメータ値を変更している！

SELECT * FROM SalesDetails
WHERE EntryDate = GETDATE() and BranchCode = @BranchCode
END

 

なぜ？

 

ストアドプロシージャは、渡されたパラメータの値を用いて、コンパイル及び最適化されます。これは、parameter sniffing と呼ばれます。

上の例では、ストアドプロシージャ GetDetails 内の SELECT は、ストアドプロシージャが呼び出された時に設定されていたパラメータ値の時に最も効率的な実行プランが選択されます。もし、@BranchCode に 0 が指定されて GetDetails が呼び出された場合には、SELECT は @BranchCode = 0 の時に最も効率的に実行できる実行プランが選択されています。しかし、実際には、このストアドプロシージャが @BranchCode = 0 で呼び出された場合には、パラメータ値は変更され、SELECT が実行される時には、@BranchCode = 100 に置き換えられています。その結果、実際の検索条件は @BranchCode = 100 であるにも関わらず、@BranchCode = 0 の時に最も効率的に実行できる実行プランを使って、この SELECT は実行されることになります。これは、せっかく CPU 時間を消費して最適な実行プランを選んだにも関わらず、それを無駄にしていることになります。

@BranchCode = 100 の場合と 0 の場合に最適な実行プランが運よく同じであれば何も問題は起こりません。しかし、最適な実行プランが同じでなければ、SELECT の実行時間は長くなります。それだけであればまだマシな方で、この SELECT による読み取りページ数が多くなれば、多くのメモリが消費され、ディスク I/O が増加し、CPU 使用率が高くなるため、他の処理にも影響を与えます。

 

どうしてもパラメータの値を変更しなければならない場合、どうするか

 

そのパラメータを参照しているステートメントに OPTION(RECOMPILE) を指定します。

 

CREATE PROCEDURE GetDetails
 (@BranchCode smallint default 0)
AS
BEGIN
-- 支店コードが指定されていない場合は本店
IF @BranchCode = 0 THEN
 SELECT @BranchCode = 100

SELECT * FROM SalesDetails
WHERE EntryDate = GETDATE() and BranchCode = @BranchCode
OPTION(RECOMPILE)
END

 

OPTION(RECOMPILE) を指定することによって、この SELECT が実行される時、この SELECT だけが再度コンパイル及び最適化され、この SELECT 実行時点の @BranchCode の値をもとに、実行プランが選択しなおされます。SELECT は、選択しなおされた実行プラン、現在の @BranchCode の値に最適な実行プランで実行されます。

しかし、この方法にはデメリットがあります。それは、コンパイル及び最適化の回数が増えることです。コンパイルや最適化は、比較的 CPU 負荷の高い処理であるため、頻繁にコンパイル及び最適化がおこなわれると、CPU 使用率が上昇する可能性があります。

そのため、まずは、ストアドプロシージャ内でパラメータの値を変更せずに済む方法を考える必要があります。

尚、SQL Server 2005 以降では、ステートメント単位の再コンパイルが可能であるため、この方法により対応できますが、SQL Server 2000 以前のバージョンでは  OPTION(RECOMPILE) は使用できないため、対応方法がありません。SQL Server 2000 では、どのようなことがあっても絶対にパラメータ値を置き換えないようにする必要があります。