アドホック クエリ（Adhoc query）

DBパフォーマンス

■ アドホック クエリ（Adhoc query）とは？

 * その場限りで使い捨てのクエリをいう
 * Adhoc : その場かぎりの、その場しのぎに、特別の

■ サンプル

-- 例1
SELECT * FROM Person WHERE id = 9;

-- 例2
SELECT * FROM Person WHERE id = 5;

■ デメリット

アドホッククエリが頻発すると... 
 * プロシージャキャッシュにアドホッククエリのコンパイル結果がどんどんたまっていき、領域を圧迫するようになる
　　→プロシージャキャッシュの無駄遣い
　　⇒メモリ圧迫
 * クエリのコンパイル処理（クエリの構文解析→最適な実行プランの作成）が何度も発生する
　　→クエリをコンパイルすると、もちろんプロセッサ時間を消費する
　　→複雑なものになれば、消費する時間も長くなる
　　⇒CPUの無駄遣い
結果... 
 * パフォーマンス悪化に繋がる
　⇒アドホッククエリはなるべく発行させないようにするべきである

解決方法

 * クエリの書き方を統一し、パラメータ化クエリを活用する
  ⇒「sp_executesql」というストアドプロシージャを使うようにする

解決例

--パラメータ化クエリ
EXECUTE sp_executesql
    -- [第1引数] クエリ文字列
    N'SELECT * FROM Person WHERE id = @ID;',
    -- [第2引数] クエリ文字列で使用する変数の宣言文
    N'@ID INT',
    -- [第3以降の引数] 宣言した変数に代入する値を指定
    @ID = 9;

■ 補足

なぜ、パラメータ化クエリにすると、アドホッククエリを避けられるのかを考える 
一旦実行すると、パラメータ（この例だとid(@ID)）に何を指定しても、実行プランを再利用される。
　→コンパイル処理が行われない（無駄なCPUを使わずにすむ）
　→キャッシュでメモリを消費することもない（無駄なメモリ消費をせずにすむ）
  ⇒パフォーマンスの向上につながる

参考文献

* 凄く分かり易い
http://blog.livedoor.jp/seplus/archives/54239233.html
* アドホッククエリによるデメリットが記述されている
http://www.atmarkit.co.jp/fdb/rensai/10_drk/06/drk01.html
* パラメータ化について
http://engineermemo.wordpress.com/2011/01/23/sql-server-%E3%81%AE%E3%82%AF%E3%82%A8%E3%83%AA%E5%AE%9F%E8%A1%8C%E6%99%82%E3%81%AE%E7%B0%A1%E6%98%93-%E5%BC%B7%E5%88%B6%E3%83%91%E3%83%A9%E3%83%A1%E3%83%BC%E3%82%BF%E3%83%BC%E5%8C%96%E3%81%AB/