【C#】【Oracle】生SQLで動的な条件を書く場合にやっておいた方が良い事
大前提としてOracleには(他のRDBにもあると思いますが)
ソフトパースとハードパースがあります。
発行されたSQLは、Oracleのパーサによってパースされつつ
SQL文自体の文法や実在しないオブジェクトに対する命令が
含まれていないか等のチェックが走ります。
問題なかった場合、そのSQL文が共有プール(SGAといいます)
にすでにキャッシュされているかをチェックし
①キャッシュされている場合はすぐにそのSQLを実行。
②キャッシュされていなかった場合は新しく実行計画を作ります。
上記①がソフトパースで、②がハードパースです。
名前から見ての通りハードパースの方がOracleの負荷が高いため
同じようなSQLを投げる際は極力ソフトパースとなるように心がけるべきです。
例えば条件に応じてショップリストを引っ張ってきたい場合
一番手っ取り早く書くなら以下のようにIN句で指定するのがお手軽です。
sql.AppendLine("SELECT"); sql.AppendLine(" shop_name"); sql.AppendLine("FROM"); sql.AppendLine(" mtb_shop"); sql.AppendLine("WHERE"); sql.AppendLine(" shop_code"); sql.AppendLine(" IN"); sql.AppendLine(" ("); for (var i = 0; i <= shopCodeList.Count - 1; i++) { if (i > 0) { sql.Append(","); } sql.Append(string.Concat(":ShopCode", i.ToString())); cmd.Parameters.Add(shopCodeList[i], OracleDbType.Varchar2); } sql.AppendLine(" )");
しかしながら上記の場合、ショップリストの件数が1~100までのパターンで
1回ずつクエリ実行したとすると、100回ハードパースがかかります。
こういった場合、状況にもよりますが*1チューニングの観点から見ると
ショップリストの上限が100までと決まっているのであれば
100に達するまでダミーのショップコードを入れ、毎回IN句に含まれるshopcodeは
100個固定にした上で実行させてしまう事をお勧めします。
ちょっと不格好ですが、これにより最初の1回のみがハードパース
残りの99回はソフトパースになりますのでパフォーマンスがあがります。