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@かしらもんじ でぇ〜

注意:
 下記ソースファイルは、本ページの管理者である「guy」が個人的に作成しました。
 このソースは作者に断り無く、個人がコピー、改造することは許可しますが、
 いかなる場合であっても、商用目的に使用することを固く禁じます。
 あと、ホームページでの公開の都合上、各ソースの先頭に全角スペースが入ってます。
 あなたがDelphiでコンパイルする前に、この全角スペースを半角スペースに変換してくださいね。

Source: QueryTester.dpr
program QueryTester;

uses
 Forms,
 QueryUnit in 'QueryUnit.pas' {Form1};

{$R *.res}

begin
 Application.Initialize;
 Application.CreateForm(TForm1, Form1);
 Application.Run;
end.


Source: QueryUnit.dfm
object Form1: TForm1
 Left = 192
 Top = 114
 Width = 468
 Height = 253
 Caption = 'QueryTester'
 Color = clBtnFace
 Font.Charset = SHIFTJIS_CHARSET
 Font.Color = clWindowText
 Font.Height = -12
 Font.Name = #65325#65331' '#65328#12468#12471#12483#12463
 Font.Style = []
 OldCreateOrder = False
 OnCreate = FormCreate
 OnDestroy = FormDestroy
 PixelsPerInch = 96
 TextHeight = 12
 object Label1: TLabel
  Left = 6
  Top = 8
  Width = 70
  Height = 12
  Alignment = taRightJustify
  AutoSize = False
  Caption = 'QueryName:'
 end
 object Label3: TLabel
  Left = 6
  Top = 52
  Width = 70
  Height = 12
  Alignment = taRightJustify
  AutoSize = False
  Caption = 'ParamName:'
 end
 object Label4: TLabel
  Left = 6
  Top = 74
  Width = 70
  Height = 12
  Alignment = taRightJustify
  AutoSize = False
  Caption = 'ParamValue:'
 end
 object Label2: TLabel
  Left = 6
  Top = 30
  Width = 70
  Height = 12
  Alignment = taRightJustify
  AutoSize = False
  Caption = 'SQLText:'
 end
 object btnReadQuery: TButton
  Left = 382
  Top = 4
  Width = 75
  Height = 20
  Caption = 'Read Query!'
  TabOrder = 4
  OnClick = btnReadQueryClick
 end
 object edtQueryName: TEdit
  Left = 78
  Top = 4
  Width = 300
  Height = 20
  TabOrder = 0
  Text = 'SELECT_CITY'
 end
 object dbgResult: TDBGrid
  Left = 4
  Top = 94
  Width = 452
  Height = 120
  Options = [dgTitles, dgIndicator, dgColumnResize, dgColLines, dgRowLines, dgTabs, dgConfirmDelete, dgCancelOnExit]
  ReadOnly = True
  TabOrder = 6
  TitleFont.Charset = SHIFTJIS_CHARSET
  TitleFont.Color = clWindowText
  TitleFont.Height = -12
  TitleFont.Name = #65325#65331' '#65328#12468#12471#12483#12463
  TitleFont.Style = []
 end
 object btnExecQuery: TButton
  Left = 382
  Top = 70
  Width = 75
  Height = 20
  Caption = 'Exec Query!'
  TabOrder = 5
  OnClick = btnExecQueryClick
 end
 object edtParameterName: TEdit
  Left = 78
  Top = 48
  Width = 300
  Height = 20
  TabOrder = 2
  Text = 'NAME'
 end
 object edtParameterValue: TEdit
  Left = 78
  Top = 70
  Width = 300
  Height = 20
  TabOrder = 3
  Text = 'Argentina'
 end
 object edtSQLText: TEdit
  Left = 78
  Top = 26
  Width = 300
  Height = 20
  TabOrder = 1
 end
end


Source: QueryUnit.pas
unit QueryUnit;

interface

uses
 Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
 Dialogs, StdCtrls, Grids, SqlExpr, DB, DBClient, DBGrids, FMTBcd;

type
 TForm1 = class(TForm)
  Label1: TLabel;
  Label2: TLabel;
  Label3: TLabel;
  Label4: TLabel;
  edtQueryName: TEdit;
  edtSQLText: TEdit;
  edtParameterName: TEdit;
  edtParameterValue: TEdit;
  btnReadQuery: TButton;
  btnExecQuery: TButton;
  dbgResult: TDBGrid;
  procedure FormCreate(Sender: TObject);
  procedure FormDestroy(Sender: TObject);
  procedure btnReadQueryClick(Sender: TObject);
  procedure btnExecQueryClick(Sender: TObject);
 private
  { Private 宣言 }
  mDS: TDataSource;
  mDB: TSQLConnection;
  mSQL: TSQLQuery;
  mCDS: TClientDataSet;
 public
  { Public 宣言 }
 end;

var
 Form1: TForm1;

implementation

{$R *.dfm}

//------------------------------------------------------------------------------
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'] := 'ATLAS_FUKUI_DEV';
 mDB.Params.Values['User_Name'] := 'ATLAS';
 mDB.Params.Values['Password'] := 'ATLAS';
 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.


Table DDL: create_table_SQLRepository.sql
create table SQLRepository (
QueryName varchar2(64),
SQLText varchar2(2000)
);

alter table SQLRepository
add(
CONSTRAINT XPK_SQLRepository PRIMARY KEY (
QueryName
)
)
;


Row Data: SQLRepository
QUERYNAME = 'SELECT_CITY' , SQLTEXT = 'select * from city where Name = :NAME'
posted by guy at 06:41 | データベース編
×

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