Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 3 of 3 total
Thread Ensuring optimisation
Wed, Dec 27 2006 6:33 AMPermanent Link

It struck me that what would be really nice for my app would be to be able
to be sure that all the queries are optimised. My ideal therefore would be
something that works on the following lines:

1. I set a "debug queries" option in the session object. This causes all
queries using this session to turn on the query plan option.
2. For each query acted, it passes the plan to an event handler so that it
can be checked for "not optimised" or similar text.
3. I can add an assert and be sure that my app is perfect.

Now, I realise this isn't likely to be sitting there or it would slow
things down, but I presume that I can do something myself by setting an
option in the query and testing the plan after the query has run. I'd be
interested if anyone has already done this and would share thoughts and
experience of it. I'm thinking along the lines of two functions:

QueryCheckPrepare(MyQueryObject);
MyQueryObject.SQL.Text := szSQL;
MyQueryObject.Open;
QueryCheckVerify(MyQueryObject);

These would then be inert in released versions, but do the business in the
debug build in the IDE.

Is this sensible?

/Matthew Jones/
Thu, Jun 7 2007 10:44 AMPermanent Link

FWIW, I've now implemented this, and spotted a few unoptimised queries as
a result. The code is below.

/Matthew Jones/

unit uSQLVerify;

interface

uses dbisamtb;

procedure SQLVerifyPrepare(xQuery : TDBISAMQuery);
procedure SQLVerifyCheck(xQuery : TDBISAMQuery; const szLocation :
string);

implementation

{$IFDEF Codesite}
uses CodesiteLogging;
{$ENDIF}


procedure SQLVerifyPrepare(xQuery : TDBISAMQuery);
begin
{$IFDEF DEBUG}
   xQuery.GeneratePlan := True;
{$ENDIF}
end;

procedure SQLVerifyCheck(xQuery : TDBISAMQuery; const szLocation :
string);
begin
{$IFDEF DEBUG}
   if Pos('UN-OPTIMIZED', xQuery.Plan.Text) > 0 then
   begin
{$IFDEF Codesite}
      CodeSite.Send('Query not optimal - ' + szLocation,
xQuery.Plan.Text);
{$ENDIF}
   end;
{$ENDIF}
end;

end.
Thu, Jun 7 2007 12:31 PMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Matthew,

<< FWIW, I've now implemented this, and spotted a few unoptimised queries as
a result. The code is below. >>

Nice job, thanks.

--
Tim Young
Elevate Software
www.elevatesoft.com

Image