2005年08月11日

SQL文をプログラムの実装から開放しチューニングする方法

経験の浅いデータベース
アプリケーションのプログラマは、
しばしばDBMS(ORACLEなどの
データベース管理システム)に対し、
重たいSQL文を発行する厄介なコードを
実装することがあります。
おそらく自分では無意識かつ
無頓着で、コンピュータが消費する
コストやパフォーマンスなど考える頭が無い開発者です。

しかし、あなたのプロジェクトはそんな開発者でも、
猫よりはましと思い協力してもらわなければ
いけない局面があることでしょう。
プログラムのパフォーマンスがSQL文が原因となる場合、
あなたは、どのように対処するのでしょうか?

SQL文の実行結果が遅い場合、
その責任を問うのは設計者にですか、
プログラマにですか?
それとも、幸いDBA(データベース管理者)が
チームメンバに居る時は、
DBAに責任を押し付けるのでしょうか?

その答えは状況によりそれぞれでしょうが、
SQL文がプログラムの中でゴリゴリに実装されていたのでは、
そのパフォーマンスを改善するにしても、
余りに貴重な時間を無駄に消費するこは間違いありません。

今回は前回と同様、
Delphiの「dbExpress」と「ORACLE」
データベースを利用する前提で、
問合せを実行するデータベースアプリケーションのSQL文を、
プログラムの実装から開放し、
SQL文を即座にチューニングできる
アプリケーションアーキテクチャをご紹介します。

全ソースは【続きを読む】をクリック!

この記事が、あなたがつくるプログラムの
アプリケーションアーキテクチャの参考になればと思います。
(Good luck!)

使用例
//------------------------------------------------
procedure CopyQuery2CDS(
 pQuery: TSQLQuery; pCDS: TClientDataSet);
var
 lCnt: Integer;
 lName: String;
begin
 pCDS.Close;
 with pCDS.FieldDefs do
 begin
  Clear;
  for lCnt := 0 to pQuery.FieldCount-1 do
  begin
   with AddFieldDef do
   begin
    Name   := pQuery.Fields[lCnt].FieldName;
    DataType := pQuery.Fields[lCnt].DataType;
    Size   := pQuery.Fields[lCnt].Size;
    Required := pQuery.Fields[lCnt].Required;
   end;
  end;
 end;
 pCDS.CreateDataSet;
 pQuery.First;
 while (not pQuery.Eof) do
 begin
  pCDS.Append;
  for lCnt := 0 to pQuery.FieldCount-1 do
  begin
   lName := pQuery.Fields[lCnt].FieldName;
   pCDS.FieldValues[lName] := pQuery.FieldValues[lName];
  end;
  pCDS.Post;
  pQuery.Next;
 end;
end;

//------------------------------------------------
procedure TForm1.FormCreate(Sender: TObject);
begin
 // DB接続オブジェクトを初期化する
 mDB := TSQLConnection.Create(Nil);
 mDB.DriverName := 'ORACLE';
 mDB.GetDriverFunc := 'getSQLDriverORACLE';
 mDB.LibraryName := 'dbexpora.dll';
 mDB.VendorLib  := 'oci.dll';
 mDB.LoginPrompt := False;
 mDB.Params.Values['DataBase'] := 'CONNECT_STRINGS';
 mDB.Params.Values['User_Name'] := 'USER';
 mDB.Params.Values['Password'] := 'PASSWORD';
 mDB.Open;
 // 問合せオブジェクトを初期化する
 mSQL := TSQLQuery.Create(Nil);
 mSQL.SQLConnection := mDB;
 // データソースオブジェクトを初期化する
 mDS := TDataSource.Create(Nil);
 mCDS := TClientDataSet.Create(Nil);
 dbgResult.DataSource := mDS;
 mDS.DataSet := mCDS;
end;

//------------------------------------------------
procedure TForm1.FormDestroy(Sender: TObject);
begin
 // DB接続オブジェクトを廃棄する
 if (mDB <> Nil) then
 begin
  mDB.Close;
  mDB.Free;
 end;
 // 問合せオブジェクトを廃棄する
 if (mSQL <> Nil) then
 begin
  mSQL.Close;
  mSQL.Free;
 end;
 // データソースを廃棄する
 if (mDS <> Nil) then
 begin
  mDS.Free;
 end;
 if (mCDS <> Nil) then
 begin
  mCDS.Free;
 end;
end;

//------------------------------------------------
procedure TForm1.btnReadQueryClick(Sender: TObject);
begin
 // SQLRepositoryよりQueryNameで指定されたSQLを取得する
 mSQL.SQL.Text := Format(
  'select * from SQLREPOSITORY where QueryName = ''%s''',
   [edtQueryName.Text]);
 mSQL.ExecSQL;
 if (mSQL.RecordCount = 0) then
  raise Exception.Create(Format(
   '該当するSQLは見つかりません。(QueryName=''%s'')',
    [edtQueryName.Text]));
 // SQLRepositoryより取得したSQLを画面に表示する
 mSQL.Open;
 edtSQLText.Text := mSQL.FieldByName('SQLTEXT').AsString;
 CopyQuery2CDS(mSQL, mCDS);
 mSQL.Close;
end;

//------------------------------------------------
procedure TForm1.btnExecQueryClick(Sender: TObject);
begin
 // SQLに指定されたパラメータをバインドし実行する
 mSQL.SQL.Text := edtSQLText.Text;
 mSQL.Prepared := True;
 mSQL.Params.ParamByName(edtParameterName.Text).AsString :=
  edtParameterValue.Text;
 mSQL.ExecSQL;
 mSQL.Open;
 CopyQuery2CDS(mSQL, mCDS);
end;

end.


【画面イメージ】

QueryTester.GIF

関連書籍


Copyright guy@かしらもんじ でぇ〜

続きを読む
posted by guy at 06:41 | データベース編

2005年08月07日

パッケージ&ストアドプロシージャでDB更新処理を高速化する方法

オンライントランザクション処理を実行する
プログラムは、複数の端末要求を即座に
処理し結果を応答するプログラムです。
ほとんどの場合、
データベースアクセスを伴うと同時に、
高い信頼性と優れたレスポンスを要求されます。

最近のデータベース管理システム(RDBMS)の大半が、
パッケージやストアドプロシージャをサポートしており、
ちょっとしたアプリケーションアーキテクチャの改善で、
データベースアクセスのパフォーマンスが
大幅に向上する可能性があります。

今回はDelphiの「dbExpress」ルーチンの
「TSQLStoredProc」を利用し、
Oracleストアドプロシージャを呼び出す方法を紹介します。

サンプルプログラムは、
簡単な表にデータを1件更新するだけのものです。
Oracleのストアドプロシージャは、
PL/SQLで記述された
処理手順をOracleが高速に実行できるよう、
コンパイルし中間コードのオブジェクトとして保持しています。
プログラムなどからストアドプロシージャが呼び出される際に、
SQL文の構文解析や再コンパイルといった
オーバーヘッドが発生しません。

従って、パフォーマンスを向上するためには、
更新の度にINSERT、UPDATE、DELETE、SELECTなどの
データ操作SQL文を毎回ちまちま発行するのではなく、
データベース更新の一連の処理をストアドプロシージャに隠蔽し、
パフォーマンスの向上を図るアーキテクチャが有効となります。

一方で複雑なデータベース更新処理を
ストアドプロシージャで実装する場合、
そのデバッグが困難となります。
ストアドプロシージャを効率よく
デバックする開発支援ツールがないと、
デバッグ作業にあなたの貴重な時間が代価として支払われます。

この記事が、あなたがつくるプログラムの
アプリケーションアーキテクチャの参考になればと思います。
(Good luck!)

ここで紹介した方法は、
パフォーマンス向上を目的とした
一つのアプリケーションアーキテクチャにすぎません。
表の正規化、
適切なインデックスの設定、
最適なSQL文の発行など基本的な
パフォーマンスチューニング技術を習得することをお忘れなく。

サンプルソースは【続きを読む】をクリック!

使用例
uses
 DBXpress,
 SqlExpr;

procedure TForm1.Button1Click(Sender: TObject);
var
 lDB: TSQLConnection;
 lSP: TSQLStoredProc;
 lTD: TTransactionDesc;
begin
 lDB := Nil;
 lSP := Nil;
 try
  // DB接続を初期化する
  lDB := TSQLConnection.Create(Nil);
  lDB.DriverName := 'ORACLE';
  lDB.GetDriverFunc := 'getSQLDriverORACLE';
  lDB.LibraryName := 'dbexpora.dll';
  lDB.VendorLib  := 'oci.dll';
  lDB.Params.Values['DataBase'] :=
   'CONNECT_STRINGS';
  lDB.Params.Values['User_Name'] := 'USER';
  lDB.Params.Values['Password'] := 'PASSWORD';
  lDB.Open;
  // DBトランザクションを開始する
  lTD.TransactionID := 1;
  lTD.IsolationLevel := xilREADCOMMITTED;
  lDB.StartTransaction(lTD);
  // DBストアドプロシージャを呼び出す
  lSP := TSQLStoredProc.Create(Nil);
  lSP.SQLConnection := lDB;
  lSP.PackageName := UpperCase('Test_Pkg');
  lSP.StoredProcName := UpperCase('Update_City');
  lSP.Prepared := True;
  lSP.ParamByName('in_Name').Value := 'Argentina';
  lSP.ParamByName('in_Capital').Value := 'Buenos';
  lSP.ParamByName('in_Population').Value :=
   32300003;
  lSP.ExecProc;
  // DBトランザクションの更新を確定する
  lDB.Commit(lTD);
  lDB.Close;
 finally
  lDB.Free;
  lSP.Free;
 end;
end;

関連書籍


Copyright guy@かしらもんじ でぇ〜

続きを読む
posted by guy at 06:33 | データベース編

広告


この広告は60日以上更新がないブログに表示がされております。

以下のいずれかの方法で非表示にすることが可能です。

・記事の投稿、編集をおこなう
・マイブログの【設定】 > 【広告設定】 より、「60日間更新が無い場合」 の 「広告を表示しない」にチェックを入れて保存する。


×

この広告は1年以上新しい記事の投稿がないブログに表示されております。