A contract developer built a system for us, and I'm trying to understand some performance
issues.

One table contains a count of sales by customers.  In this snippet, we simply clear all
the counts to zero:

s := 'update wf_sales ';
s := s + 'set SalesCount = 0';
with aQuery do
 begin
  close;
  databasename := 'dbMyEvents';
  sql.clear;
  sql.add(s);
  execsql;
  close;
 end;

I timed that "execsql" statement and was surprised by how long it took:
To go through 15,382 entries took 4 minutes 34 seconds!  I watched the disk light, and
there was little disk activity.

I have no idea even where to start to look for clues about why this is taking so long.

Any tips or suggestions?  Thanks to anyone!

Kevin

Firstly is this single user or multi user, f/s or c/s. If f/s then check out network performance.


If you have DBSys you can try running the query in it with generate plan checked (if you don't have it you can download it from Tim's website). That will tell you the "real" time taken. Otherwise if you have the source and can recompile you can alter the routine to


with aQuery do
 begin
  close;
  GeneratePlan := True;  <<<<<<<<<<<<<<<<<
  databasename := 'dbMyEvents';
  sql.clear;
  sql.add(s);
  execsql;
  Plan.SaveToFile(filename); <<<<<<<<<<<<<<<<<<
  close;
 end;

In either case post the plan here and that will, possibly, help, but I doubt it with a simple query such as yours.

Since it looks as though a query is being reused one thought is - Is the query hooked up to any visual components that are being refreshed as the query progresses? If so this will slow it down dramatically.

Also if FlushBuffers is set to True this will have a considerable impact.

Finally if there are any custom functions built in which are fired when SalesCount is altered?

Roy Lambert [Team Elevate]

Kevin

try to enclosure the update within a transaction. I think you will se more
performance on it.

Eduardo

Kevin,

<< I timed that "execsql" statement and was surprised by how long it took:
To go through 15,382 entries took 4 minutes 34 seconds!  I watched the disk
light, and there was little disk activity. >>

How did you time the statement ?  You can get the actual execution time for
the statement like this:

s := 'update wf_sales ';
s := s + 'set SalesCount = 0';
with aQuery do
 begin
  close;
  databasename := 'dbMyEvents';
  sql.clear;
  sql.add(s);
  execsql;
  ShowMessage('Execution time is '+FloatToStr(ExecutionTime)+' secs');
  close;
 end;

Also, as already asked, it is important to know the architecture of the
application - local/single-user, multi-user, file-sharing, multi-user C/S,
etc..

--
Tim Young
Elevate Software
www.elevatesoft.com