Recordsetの処理時間について

質問

現在VC++を用いてSQL Server Express 2005のデータベースに対してSQL文を実行しています。
そこで、下記のような現象が発生し、解決できずに困っています。

[現象]
複数のデータベースが存在する(約300)環境に対して、masterデータベースに接続し、個々のデータベースに対してデータベース名を明示的に記述したSQLを実行。
データベース接続したデータベースと異なるデータベースに対するSQLの処理は1ms~500msと処理時間がばらつきます
(SQL文はSELECT data1 FROM Db1.dbo.table1 where data1=1;という感じで各テーブルには約3000レコード、SQLの結果は300件程度。Recordset Openメソッドの引数はCursorType：adOpenForwardOnly,Options：adLockReadOnly,LockType：adCmdTextです。)。

まったく同一のSQL文なので、データベース接続後、1回目は時間がかかるのは理解できますが、それ以降の処理を連続で繰返しているだけなので2回目以降処理時間が大きくばらつくことが不明です。
これは同一データベースに対して繰返しても、別々のデータベースに対して繰返してもほぼ同様の現象が発生します。

[確認したこと]
- Windows認証、SQL Server認証いずれにおいても同様の傾向。
- 自プロセスあるいは他プロセスからターゲットとなるデータベースに先に接続した状態のままで実行すれば、処理時間のばらつきが1ms~80ms程度

本来、データベース毎に接続-->SQL実行-->解放を繰返せばよいのですが、Vistaにおいてはメモリリークが発生するため、この方法は採用できません。

現状、別プロセスでSQL文を実行するデータベースだけ、先に接続し、処理が完了すればこのプロセスを終了することで回避を考えているのですが、この方法では、SQLServerの負荷をかけてしまうため、よりよい方法が無いか教えてください。

よろしくお願い致します。

回答No.3

AKARI0418

>再利用されるためにはストアドプロシージャにしておく必要があるのでしょうか?

ストアドに登録したとしても、
提示したクエリは再利用されないと思います。

なぜなら、実行するたびに対象となるリソース(テーブル)が変わるため、
効率の良いアクセス方法を再度計画しなおす必要があるためです。

提示したクエリであれば、
ストアドにすることの利点はSQLとして送信する文字列長の短縮、
そのSQLのコンパイルにかかる時間の短縮程度です。

>ユニオンクエリを行なうために、各データベースのテーブルの存在チェックでこのようなクエリを必要としています。
他のクエリと同様の方法でパラメータ化し効率化できればと考えています。

SQLだけで処理をすることが、
効率的であるかどうか判断が必要かもしれません。

CLR SQLの使用を検討されてみてはどうでしょうか？

回答No.2

AKARI0418

DECLARE @SQL nvarchar(1024)
SET @SQL = N'SELECT * FROM ' + @TableName + ';'
EXECUTE sp_executesql @SQL

こんな感じです。

ただし、実行計画が再利用されなくなると思うので、
処理にばらつきは出ると思いますよ。

どこまでのばらつきを許容できるか判断してください。

あまりに遅いテーブルがある場合は、
非クラスタインデックスが正しく設定されているかなどをチェックしてください。

後はインデックスの断片化を解消しておくことも大事です。

お礼

AKARI0418様、回答ありがとうございます。

教えて頂いた方法を試し、処理時間のばらつきがどれくらいか確認してみます。

再利用されるためにはストアドプロシージャにしておく必要があるのでしょうか?

ユニオンクエリを行なうために、各データベースのテーブルの存在チェックでこのようなクエリを必要としています。
他のクエリと同様の方法でパラメータ化し効率化できればと考えています。

回答No.1

AKARI0418

SQL Server の使用しているメモリ(キャッシュおよび実行プラン)の状態による差異じゃないですか？

特に、ページングファイルを利用している場合は差が出ても不思議ではありません。

実行プランがアドホックなクエリとしてあつかわれているか、
もしくは再利用されているのか。

データの断片化具合も考えられますね、
ページ数が多いテーブルと少ないテーブルでは、
取得に時間がかかってもおかしくありません。

状況がわからないので、的を射ていないかもしれませんが、
参考までに。

お礼

AKARI0418様、回答ありがとうございます。

自動パラメータ化されているかと考えていましたが、実際に発行しているSQL文においてTOP句、GROUP BY句、共通テーブル式を含むものがあり、自動パラメータ化の対象から外れていました。
(http://www.microsoft.com/japan/sqlserver/2005/ssj/tips/04.mspx#EKB参照)

これらをsp_executesqlを用いてパラメータ化することで、処理時間のばらつきが対象データベースに接続した場合とあまり変わらない状況まで改善しました。

ただし、データベースやテーブル名を切り替えるパラメータはsp_executesqlではSQL文をうまく表現できないため、まだ試せていません。

例えば、SELECT Field1 FROM [DB1].[dbo].[TBL1] WHERE Field1 = 0;
というようなSQL文においてDB1やTBL1をパラメータ化するにはどのように明記すればよいでしょうか?

あるいはsp_executesqlでは、データベースやテーブル、フィールド名はパラメータ化できないのでしょうか?
(このような場合は、ストアド プロシージャあるいはParameterクラスを使う必要がありますか?)

よろしくお願い致します。