I've a quite complex query that does execute in 1.5 seconds in DBSYS,
but requires:

2.0 seconds in my program, if run with tables (the same involved in the
query) CLOSED

9.2 seconds in my program, if run with tables (the same involved in the
query) OPEN

Why such a difference?
Also where tables are not connected to any VCL db-control, the
difference is exactly the same.

Using 4.37b3 + D2010

Thx
A.

> Why such a difference?

I'd be looking at things like the strict/optimistic change detection, and other
such parameters that affect lookups.

/Matthew Jones/

> I'd be looking at things like the strict/optimistic change detection,
> and other such parameters that affect lookups.

thanks Matthew, i discovered that the execution time was related to
engine's buffers (i've customized the engine) and the tables, if opened
and not used, seems to keep some (a lot?) of buffers busy, slowing down
the query.

A.

Indeed. In some cases, less buffers can speed things up. There is also an
"exclusive" mode, and one of my applications uses this when it knows it is in
"single thread" mode to get a small speed up. Optimisation becomes something of
measure and adjust to find the best for your situation.

/Matthew Jones/

Alessandra,

<< thanks Matthew, i discovered that the execution time was related to
engine's buffers (i've customized the engine) and the tables, if opened and
not used, seems to keep some (a lot?) of buffers busy, slowing down the
query. >>

The tables being open or not should not affect the query execution time
(other than the additional overhead to actually open the tables).  However,
if you increase the memory buffering settings too high, you can most
definitely affect the performance in a negative way.  How high did you set
the engine's buffering settings ?

Tim Young
Elevate Software
www.elevatesoft.com

> The tables being open or not should not affect the query execution
> time (other than the additional overhead to actually open the
> tables).  However, if you increase the memory buffering settings too
> high, you can most definitely affect the performance in a negative
> way.  How high did you set the engine's buffering settings ?

Hi, Tim, i multiply the default engine values by a factor of 500:

 MaxTableDataBufferSize:=DEFAULT_RECORD_BUFFER_SIZE*mult; // total
amount of memory to use for record buffers in the buffer cache per
physical table
 MaxTableDataBufferCount:=DEFAULT_RECORD_BUFFER_COUNT*mult; // maximum
number of record buffers to allow in the buffer cache per physical table

 MaxTableIndexBufferSize:=DEFAULT_PAGE_BUFFER_SIZE*mult; // total
amount of memory to use for index page buffers in the buffer cache per
physical table
 MaxTableIndexBufferCount:=DEFAULT_PAGE_BUFFER_COUNT*mult; // maximum
number of index page buffers to allow in the buffer cache per physical
table

 MaxTableBlobBufferSize:=DEFAULT_BLOCK_BUFFER_SIZE*mult; // total
amount of memory to use for BLOB block buffers in the buffer cache per
physical table
 MaxTableBlobBufferCount:=DEFAULT_BLOCK_BUFFER_COUNT*mult; // maximum
number of BLOB block buffers to allow in the buffer cache per physical
table

in further experiments, i noticed that with such a value the tables
being open and NOT used (no forms, no vcl attached) slow down the query
a lot.
In the same scenario:
Using mult:=1, the query executes in about 1 second
Using mult:=1500, the query executes in about 0,4 seconds

Can you please advice about a value of "mult" suitable in most cases? i
guess that it can (and should) be different in case of main pc (using
local tables) and netword pcs (using shared tables)

Thanks in advance Tim!

--
Alessandra

Alessandra,

<< in further experiments, i noticed that with such a value the tables being
open and NOT used (no forms, no vcl attached) slow down the query a lot.
In the same scenario:
Using mult:=1, the query executes in about 1 second
Using mult:=1500, the query executes in about 0,4 seconds >>

Are those numbers transposed ?  I would expect the 1500 multiplier to be the
slower, if what you're saying is correct.

<< Can you please advice about a value of "mult" suitable in most cases? i
guess that it can (and should) be different in case of main pc (using local
tables) and netword pcs (using shared tables) >>

Yes, if you're using local tables, then the defaults should be fine.  With
shared, networked tables, you'll probably not want to go much above 1 or 2
MB.  Anything higher is just going to result in excessive overhead without
an accompanying increase in performance.  This is because the cache is
constantly needing to be dumped/refreshed due to changes by other users, and
isn't a "permanent" cache.

If you're doing a lot of batch processing, then you should look into using
this property (it's new) to optimize any sequential access:

http://www.elevatesoft.com/manual?action=viewprop&id=dbisam4&product=delphi&version=7&comp=TDBISAMDataSet&prop=LocalReadSize

Tim Young
Elevate Software
www.elevatesoft.com