フォーラム


ゲスト  

ようこそ ゲスト さん。このフォーラムに投稿するには 登録が必要です。

ページ: [1]
トピック: SQL のパフォーマンスを低下させないためには?
DEKO
管理者
投稿数: 2645
SQL のパフォーマンスを低下させないためには?
on: 2013/05/19 17:00 Sun

ここは Delphi Forum ですから、Delphi 絡みの話になります。SQL 文自体のパフォーマンスは各自でどうにかしてください。

例えば、ループで回して SQL を投げるといった場合、

var
i, l: Integer
begin
for i:=1 to 100 do
begin
with Query1.SQL do
begin
Clear;
Add('Insert Into TBL_DATA01');
Add('( ');
Add(' ID, ');
Add(' NAME, ');
Add(' NOTE ');
Add(') ');
Add('Values ');
Add('( ');
Add(' :ID, ');
Add(' :NAME, ');
Add(' :NOTE ');
Add(') ');
ParamByName('ID' ).AsInteger := i;
ParamByName('NAME').AsString := 'AAA';
ParamByName('NOTE').AsString := 'BBB';
ExecSQL;
end;
for l:=1 to 100 do
begin
with Query1.SQL do
begin
Clear;
Add('Insert Into TBL_DATA02');
Add('( ');
Add(' ID, ');
Add(' SEQ, ');
Add(' SUBFIELD01, ');
Add(' SUBFIELD02, ');
Add(' SUBFIELD03, ');
Add(' SUBFIELD04, ');
Add(' SUBFIELD05, ');
Add(' SUBFIELD06, ');
Add(' SUBFIELD07, ');
Add(' SUBFIELD08, ');
Add(' SUBFIELD09, ');
Add(' SUBFIELD10 ');
Add(') ');
Add('Values ');
Add('( ');
Add(' :ID, ');
Add(' :SEQ, ');
Add(' :SUBFIELD01, ');
Add(' :SUBFIELD02, ');
Add(' :SUBFIELD03, ');
Add(' :SUBFIELD04, ');
Add(' :SUBFIELD05, ');
Add(' :SUBFIELD06, ');
Add(' :SUBFIELD07, ');
Add(' :SUBFIELD08, ');
Add(' :SUBFIELD09, ');
Add(' :SUBFIELD10 ');
Add(') ');
ParamByName('ID' ).AsInteger := i;
ParamByName('SEQ' ).AsInteger := l;
ParamByName('SUBFIELD01').AsString := 'AAA';
ParamByName('SUBFIELD02').AsString := 'BBB';
ParamByName('SUBFIELD03').AsString := 'CCC';
ParamByName('SUBFIELD04').AsString := 'DDD';
ParamByName('SUBFIELD05').AsString := 'EEE';
ParamByName('SUBFIELD06').AsString := 'FFF';
ParamByName('SUBFIELD07').AsString := 'GGG';
ParamByName('SUBFIELD08').AsString := 'HHH';
ParamByName('SUBFIELD09').AsString := 'III';
ParamByName('SUBFIELD10').AsString := 'JJJ';
ExecSQL;
end;
end;
end;
end;

 
これはもの凄く効率が悪いです。SQL 文の構築 (ビルド) を 10000 回やっていますが、実際には 2 回でいいハズですから。

var
i, l: Integer
begin
with Query1.SQL do
begin
Clear;
Add('Insert Into TBL_DATA01');
Add('( ');
Add(' ID, ');
Add(' NAME, ');
Add(' NOTE ');
Add(') ');
Add('Values ');
Add('( ');
Add(' :ID, ');
Add(' :NAME, ');
Add(' :NOTE ');
Add(') ');
end;
with Query2.SQL do
begin
Clear;
Add('Insert Into TBL_DATA02');
Add('( ');
Add(' ID, ');
Add(' SEQ, ');
Add(' SUBFIELD01, ');
Add(' SUBFIELD02, ');
Add(' SUBFIELD03, ');
Add(' SUBFIELD04, ');
Add(' SUBFIELD05, ');
Add(' SUBFIELD06, ');
Add(' SUBFIELD07, ');
Add(' SUBFIELD08, ');
Add(' SUBFIELD09, ');
Add(' SUBFIELD10 ');
Add(') ');
Add('Values ');
Add('( ');
Add(' :ID, ');
Add(' :SEQ, ');
Add(' :SUBFIELD01, ');
Add(' :SUBFIELD02, ');
Add(' :SUBFIELD03, ');
Add(' :SUBFIELD04, ');
Add(' :SUBFIELD05, ');
Add(' :SUBFIELD06, ');
Add(' :SUBFIELD07, ');
Add(' :SUBFIELD08, ');
Add(' :SUBFIELD09, ');
Add(' :SUBFIELD10 ');
Add(') ');
end;
for i:=1 to 100 do
begin
with Query1.SQL do
begin
ParamByName('ID' ).AsInteger := i;
ParamByName('NAME').AsString := 'AAA';
ParamByName('NOTE').AsString := 'BBB';
ExecSQL;
end;
for l:=1 to 100 do
begin
with Query2.SQL do
begin
Clear;
ParamByName('ID' ).AsInteger := i;
ParamByName('SEQ' ).AsInteger := l;
ParamByName('SUBFIELD01').AsString := 'AAA';
ParamByName('SUBFIELD02').AsString := 'BBB';
ParamByName('SUBFIELD03').AsString := 'CCC';
ParamByName('SUBFIELD04').AsString := 'DDD';
ParamByName('SUBFIELD05').AsString := 'EEE';
ParamByName('SUBFIELD06').AsString := 'FFF';
ParamByName('SUBFIELD07').AsString := 'GGG';
ParamByName('SUBFIELD08').AsString := 'HHH';
ParamByName('SUBFIELD09').AsString := 'III';
ParamByName('SUBFIELD10').AsString := 'JJJ';
ExecSQL;
end;
end;
end;
end;

 
2 つのクエリコントロールを使っている事に注意してください。

これだけだとよくある話なのですが、まだ問題点が残っています。それは SQL 文の構築に TStrings.Add() を使っている箇所です。クエリコントロールは SQL プロパティが更新される度に内部で SQL を構築します。パラメータの解析なども都度行われます。つまり、Add() される度に何度も SQL のパースが行われるという事です。大した問題ではないように思えるかもしれませんが、構文解析というのは結構コストが掛かります。 1 行追加されたからと言って、その 1 行だけをパースする訳にはいきません。1 行追加されたら SQL 文は先頭から再度パースされます。

この問題を回避するには、SQL プロパティへの代入を一度で済ますしかありません。ですが、String 型の変数を使うのはデバッグ時に面倒です ("デバッグしやすい SQL の書き方" を参照) し、視認性もよくありません。ワーク (TStringList) を使って回避するのがいいでしょう。

var
i, l: Integer
dSQL: TStringList;
begin
dSQL := TStringList.Create;
try
with dSQL do
begin
Clear;
Add('Insert Into TBL_DATA01');
Add('( ');
Add(' ID, ');
Add(' NAME, ');
Add(' NOTE ');
Add(') ');
Add('Values ');
Add('( ');
Add(' :ID, ');
Add(' :NAME, ');
Add(' :NOTE ');
Add(') ');
Query1.SQL.Text := Text;
end;
with dSQL do
begin
Clear;
Add('Insert Into TBL_DATA02');
Add('( ');
Add(' ID, ');
Add(' SEQ, ');
Add(' SUBFIELD01, ');
Add(' SUBFIELD02, ');
Add(' SUBFIELD03, ');
Add(' SUBFIELD04, ');
Add(' SUBFIELD05, ');
Add(' SUBFIELD06, ');
Add(' SUBFIELD07, ');
Add(' SUBFIELD08, ');
Add(' SUBFIELD09, ');
Add(' SUBFIELD10 ');
Add(') ');
Add('Values ');
Add('( ');
Add(' :ID, ');
Add(' :SEQ, ');
Add(' :SUBFIELD01, ');
Add(' :SUBFIELD02, ');
Add(' :SUBFIELD03, ');
Add(' :SUBFIELD04, ');
Add(' :SUBFIELD05, ');
Add(' :SUBFIELD06, ');
Add(' :SUBFIELD07, ');
Add(' :SUBFIELD08, ');
Add(' :SUBFIELD09, ');
Add(' :SUBFIELD10 ');
Add(') ');
Query2.SQL.Text := Text;
end;
finally
dSQL.Free;
end;
for i:=1 to 100 do
begin
with Query1.SQL do
begin
ParamByName('ID' ).AsInteger := i;
ParamByName('NAME').AsString := 'AAA';
ParamByName('NOTE').AsString := 'BBB';
ExecSQL;
end;
for l:=1 to 100 do
begin
with Query2.SQL do
begin
Clear;
ParamByName('ID' ).AsInteger := i;
ParamByName('SEQ' ).AsInteger := l;
ParamByName('SUBFIELD01').AsString := 'AAA';
ParamByName('SUBFIELD02').AsString := 'BBB';
ParamByName('SUBFIELD03').AsString := 'CCC';
ParamByName('SUBFIELD04').AsString := 'DDD';
ParamByName('SUBFIELD05').AsString := 'EEE';
ParamByName('SUBFIELD06').AsString := 'FFF';
ParamByName('SUBFIELD07').AsString := 'GGG';
ParamByName('SUBFIELD08').AsString := 'HHH';
ParamByName('SUBFIELD09').AsString := 'III';
ParamByName('SUBFIELD10').AsString := 'JJJ';
ExecSQL;
end;
end;
end;
end;

 
パラメータのチェックを回避するだけなら、クエリコントロールの ParamCheck を False にしてやればいいのですが、これを True にしたと同時にパラメータチェックが行われる訳ではありません。TQuery 等の場合 ParamCheck を True にした上で RefreshParams() を明示的に呼び出す必要があり、忘れるとエラーを拝むことになります…よってこの手法はオススメしません。

このトピックでの要点は 3 つだけです。

  • パラメータクエリを使う
  • SQL 文はクエリコントロールが増える事になってもループ外でビルドする
  • SQL 文はワーク (TStringList) で組み立ててから SQL プロパティにセットする。

パラメータクエリを活用すれば、ループ内で SQL 文のビルドを行わずに済みます。
DB 関連のアプリを作ってらっしゃる方は実測してみるのが一番だと思います。テーブルのフィールド数が多い程効果 (差) は大きくなります。

See Also:
[SQL インジェクション]
http://ht-deko.minim.ne.jp/delphiforum/?vasthtmlaction=viewtopic&t=1162
[デバッグしやすい SQL の書き方]
http://ht-deko.minim.ne.jp/delphiforum/?vasthtmlaction=viewtopic&t=1163

おかぽん
メンバー
投稿数: 4
Re: SQL のパフォーマンスを低下させないためには?
on: 2013/05/22 15:08 Wed

Delphi5でBDEのTQueryしか使ったことがないのですが・・・
TStringsにはBeginUpdateEndUpdateメソッドがあります。
SQLプロパティは、TStrings型なので、BeginUpdateEndUpdateが使えます。
SQL文の変更前にBeginUpdate、変更後にEndUpdateしておけば、SQL のパースが何度も発生しないはずですが、
DelphiのバージョンやQueryの種類(TQuery、TSQLQueryとか)では動作が異なるんでしょうか?

with Query1.SQL do
begin
BeginUpdate;
Clear;
Add('Insert Into TBL_DATA02');
(略)
Add(' :SUBFIELD10 ');
Add(') ');
EndUpdate;
end;
DEKO
管理者
投稿数: 2645
Re: SQL のパフォーマンスを低下させないためには?
on: 2013/05/22 15:42 Wed

引用 OkapOn on 2013/05/22 15:08 Wed
SQL文の変更前にBeginUpdate、変更後にEndUpdateしておけば、SQL のパースが何度も発生しないはずですが、
DelphiのバージョンやQueryの種類(TQuery、TSQLQueryとか)では動作が異なるんでしょうか?

 
理屈的に BeginUpdateEndUpdate を使う手法は有効だと思います。TStringList を別に用意しなくてもいいので楽ですし、むしろ推奨します。

別の TStringList をワークに使っているのはパラメータ絡みの問題があったからだった (多分、バッドノウハウですが) と思うのですが、ちょっと思い出せません (^^;A 曖昧な事を書いてもアレなので、おかぽんさんの手法をオススメしときます。

# XE とかで試す限り BeginUpdateEndUpdate で期待した結果が得られます。

ページ: [1]
WP Forum Server by ForumPress | LucidCrew
バージョン: 1.7.5 ; ページロード: 0.041 sec.