I have an application that does repeated calls to the same DBISAM query in a thread.
The thread starts by initialising the query, and then calls .Close and .Open to get
the latest results. However, over a period of about an hour, it gets progressively
slower to perform its actions, eating more and more processor.

In preparing this post, I found the cause, but it needs fixing in DBISAM. The code
is effectively this:

xQuery.Close;
xQuery.ParamByName('TIMEOUT').AsDateTime := Now;
xQuery.GeneratePlan := True;
xQuery.Plan.Text := '';
xQuery.Open;
if Pos('UN-OPTIMIZED', xQuery.Plan.Text) > 0 then
  ReportDatabaseIssue;
if not xQuery.EOF then
   result := true;

After an hour, the resulting xQuery.Plan.Text for this query is  13182102 bytes in
size! Note that the code sets it to an empty string, but after the call it is set
to the plans of every query ever made, which at 4 times a second over an hour is a
few...

Looking at the code, the problem is that the TDBISAMQuery clears its copy of the
Plan, but TDataQuery never does. It just keeps on appending to its own TStringList,
and never has a reset. Perhaps the ExecuteStatement routine should clear the plan
at its start to fix this.

This can be worked around by adding these lines:
   if assigned(xQuery.StmtHandle) then
      xQuery.StmtHandle.Plan.Text := '';
Fortunately, the underlying query object is public enough to allow its Plan to be
emptied. The thread is now back to its 1ms query time, instead of its 250ms time
that it was getting to (including my Pos...).

I have reported this as an issue but the workaround and the fact that most people
don't want to use a plan in any sort of live code mean it is not significant to
anyone.

/Matthew Jones/

I would suggest you submit this direct into incident reports as Tim
might not see it here in NGs for a few days

http://www.elevatesoft.com/incident?action=submit&category=dbisam

He likely will be updating DBISAM for XE4 soon as well so he can roll in
changes at the same time.

Raul

On 6/20/2013 11:30 AM, (Matthew Jones) wrote:
> I have an application that does repeated calls to the same DBISAM query in a thread.
> The thread starts by initialising the query, and then calls .Close and .Open to get
> the latest results. However, over a period of about an hour, it gets progressively
> slower to perform its actions, eating more and more processor.
>
> In preparing this post, I found the cause, but it needs fixing in DBISAM. The code
> is effectively this:
>
> xQuery.Close;
> xQuery.ParamByName('TIMEOUT').AsDateTime := Now;
> xQuery.GeneratePlan := True;
> xQuery.Plan.Text := '';
> xQuery.Open;
> if Pos('UN-OPTIMIZED', xQuery.Plan.Text) > 0 then
>     ReportDatabaseIssue;
> if not xQuery.EOF then
>    result := true;
>
> After an hour, the resulting xQuery.Plan.Text for this query is  13182102 bytes in
> size! Note that the code sets it to an empty string, but after the call it is set
> to the plans of every query ever made, which at 4 times a second over an hour is a
> few...
>
> Looking at the code, the problem is that the TDBISAMQuery clears its copy of the
> Plan, but TDataQuery never does. It just keeps on appending to its own TStringList,
> and never has a reset. Perhaps the ExecuteStatement routine should clear the plan
> at its start to fix this.
>
> This can be worked around by adding these lines:
>    if assigned(xQuery.StmtHandle) then
>       xQuery.StmtHandle.Plan.Text := '';
> Fortunately, the underlying query object is public enough to allow its Plan to be
> emptied. The thread is now back to its 1ms query time, instead of its 250ms time
> that it was getting to (including my Pos...).
>
> I have reported this as an issue but the workaround and the fact that most people
> don't want to use a plan in any sort of live code mean it is not significant to
> anyone.
>
> /Matthew Jones/
>

As I said in the original, I have reported it as an issue. Tim has also said it
will be fixed in the next update. I don't think it is too critical to anyone
though.

/Matthew Jones/