I have an app that has many forms, the data for each is extracted by (usually) a single
query component which is created and destroyed on entry to and exit from the form.

On some forms the query performance is very slow.  I have picked on one form which is
particularly slow and put some timing points in to see where the time is going.  In
addition I have put all the form init code in a single procedure in order to make things
easier to see  The longest query is taking 5 seconds which is a long time considering the
size of the table and results set.  

The state of the data is checked every 20 seconds which also servers to prevent a session
timeout.

If I add a new session component connected to the same server and change the init proc to
use the new SessionName then the queries are much quicker 800mS instead of 5000mS.  I have
tried to ensure that both session components are set the same way including PrivateDir.

Does anyone have any good ideas (please)?

Peter

Peter

First thing to do is produce execution plans for the queries. Those will, usually, identify the cause of the slow performance (eg an extra index or two needed). It also means that you're not just looking at time (Windows & DBISAM caching can make a massive difference in timings).

The other general cause of slowdown is linked controls, lookup tables, or events.

The puzzling thing about what you report is that just using a different session component speeds things up so it could be one of its properties. When you created the new session did you drop a blank one on the form? If so try a cut'n'paste instead to make sure the properties are the same. What you'll have to do is ctrl-C on the existing session, then change its session name then ctrl-V to paste the copy otherwise you get an error.

Roy Lambert

Hi Roy

Thanks for the guidance, my tests with two different sessions weren't entirely fair as one
included the offending line (below) and the other didn't.  The queries are far from
optimized at the moment but it doesn't appear to be the source of the problem.  Your
comments did however prompt me to look in a different place.  I went through the init
stages of the app reming out routines that ran queries, eventually one produced the
desired effect.  The init routine runs only once and is finished long before the one I
used for my measurements is run.

The offending routine (attached) is the only one in the app that uses blobs, they hold
images.  When the routine runs during init it extracts blobs from the table and writes
them to disk.  I presumed that the delay would be in write to disk however removing these
lines produced no change.  In the end the offending line is the one that checks to see
that the blob contains data.  There are two lines with this code, however only the second
produces a result, there aren't many records with an "ImageIndex" of less than 10.

   if TBlobField(Table.FieldByName('Image')).BlobSize > 0 then

I can't see why this line should produce a permanent effect on the accessing of data.
Without the routine the queries in the entire app are a lot faster.  My later tests are
using localhost rather than a proper network, the times are 5000mS with the line and 300mS
without the line.

Any ideas?

Thanks

Peter


Roy Lambert wrote:

Peter

First thing to do is produce execution plans for the queries. Those will, usually,
identify the cause of the slow performance (eg an extra index or two needed). It also
means that you're not just looking at time (Windows & DBISAM caching can make a massive
difference in timings).

The other general cause of slowdown is linked controls, lookup tables, or events.

The puzzling thing about what you report is that just using a different session component
speeds things up so it could be one of its properties. When you created the new session
did you drop a blank one on the form? If so try a cut'n'paste instead to make sure the
properties are the same. What you'll have to do is ctrl-C on the existing session, then
change its session name then ctrl-V to paste the copy otherwise you get an error.

Roy Lambert

Peter


I bet you're fun at parties

   Table := TDBISamQuery.Create(nil);

<VBCG>

Now seriously. If there are a lot of them, and they are a decent size I suppose they could be screwing free ram but that's about all I can come up with. However, your post is that its the test that's the problem not the actual extraction and writing to disk I'm baffled. I think a full answer will have to wait for Tim since I have no idea why.

A couple of observations which have nothing to do with the problem (I had a great suggestion then realised you weren't on ElevateDB :


    0..9: begin
             if TBlobField(Table.FieldByName('Image')).BlobSize > 0 then ;
               TBlobField(Table.FieldByName('Image')).SaveToFile(Folder + '\' + Table.FieldByName('FileName').AsString);
           end;

The if TBlobField(Table.FieldByName('Image')).BlobSize > 0 then ; achieves nothing. The end of line ; effectively makes the next line stand alone so it will always be executed.

finally
   if Table <> nil then
   begin
     Table.Free;
     Table := nil;
   end;
 end;

could be better written as

finally
FreeAndNil(Table);
end;


Roy Lambert [Team Elevate]

Hi Roy

Hmmm, there are a lot of "Tables" where the routines are simple.

I hadn't noticed the colon, it shouldn't be there.

Thanks for the "Freeand Nil" much more elegant than my alternative.

I will go back and have another look at the problem (anything not to have to watch the
tennis) and create a nice simple project that demonstrates the problem.  Perhaps Tim can
come up with a gem.

Cheers

Peter

Roy

Thanks for pointing out the ; problem.  It meant, of course, that the problem wasn't on
the line I though it was.

I have done a quick demo to prove the problem and have tried it on another machine with
similar results.  The project and tables are attached.  

Push the "Fill Combo" a couple of times and note the times.  Then push the "Save BLOB's".
Then push the "Fill Combo" a couple of times and see the time differences.

Thanks


Peter

Roy

Thanks for pointing out the semi colon error.  It meant, of course, that the problem
wasn't on the line I though it was.

I have done a quick demo to prove the problem and have tried it on another machine with
similar results.  The project and tables are attached.  

Push the "Fill Combo" a couple of times and note the times.  Then push the "Save BLOB's".
Then push the "Fill Combo" a couple of times and see the time differences.

Thanks


Peter

Peter

Peter,

Please don't post attachments in regular newsgroups - there is a special binaries one.

I recompiled your test app (D2007, DBISAM 4.29 b2) and ran it and here are results:

Fillcombo :
734
627
656

Save BLOBs:
1828


Fill combo
703
672
687

Save BLOB
1593

Fill combo
687
672


Running outside IDE took approx 100ms off the FillCombo (so most runs were in the 640ms range)


Raul



Peter Lee wrote:

Roy

Thanks for pointing out the semi colon error.  It meant, of course, that the problem
wasn't on the line I though it was.

I have done a quick demo to prove the problem and have tried it on another machine with
similar results.  The project and tables are attached.  

Push the "Fill Combo" a couple of times and note the times.  Then push the "Save BLOB's".
Then push the "Fill Combo" a couple of times and see the time differences.

Thanks


Peter

Peter

Peter


First, as Raul says, please don't post large attachments in the normal ngs, especially when you do it twice.

Secondly, unlike Raul I do get the same sort of time differences that you see (c5000ms after saving blobs c600ms before). I'm using D6, DBISAM V25 and Vista. This has made me aware that you haven't given us your environment information.

Can you let us have the Delphi, DBISAM and OS information please.

Roy Lambert [Team Elevate]

Peter,

I'm seeing figures of:

Fill Combo:

594
563
579
563

Save BLOBs:

719
719
703
719

And I do not see any difference in the timings, either before or after the
BLOBs are saved.

--
Tim Young
Elevate Software
www.elevatesoft.com