At 11:07 PM 16/01/2004 +0000, you wrote:

>Colin;
>
>I read your ealier posting wherein you said:
>
> > There is a big change in the OAT and the Next, could this be the
> > reason some users are complaining about searches that use to take 5
> > secs , now take 1min 10 secs.
>
>I have been told many times in this forum that IB / FB performance
>starts with well written queries.

That is a fundamental, a given. Since you are using Delphi, let me assure
you that a TTable is the worst kind of query there is.

>Is it possible that the query that formerly ran in 5 seconds was
>retrieving data from a table of half as many records as that same table
>now has?

It's always possible that a table will grow; but growth won't cause the
query to begin returning row in any longer time. If a query takes 14 *
longer than it used to, it means something else.

> > We thought the same about clearing the database connections,

>We have done a conceptually similar thing, however, we look for this
>file in a background (sleeping) thread every 5 minutes or so. This
>allows us to FORCE a user off even if they've left the building.
>However, we don't yet have an automated way to place this file and
>remove it each night, every night. Plus, this is a very interesting
>technical question that I'd very much like to understanding fully.

Your conceptual problem is that you both believe that transactions that
don't die until you close the connection. You learned this misconception
no doubt by moving from Delphi + Paradox to Delphi + IB + the Paradox
database engine.

The TDatabase component encapsulates one connection + 1 transaction. In
Paradox, there are no transactions, so TDatabase encapsulates Paradox
perfectly. To make IB (and other supported RDBMS that have authentic
multi-user transaction architectures) into Paradox, the IB developers were
persuaded to introduce COMMIT WITH RETAIN. As a result, the BDE (which is
the Paradox database engine) automagically performs a soft commit when the
application calls Post. This was encapsulated into the TDatabase as
CommitRetaining. When you have the TDatabase set up to Autocommit (the
default) you get CommitRetaining.

The other killer default with the one transaction-one connection model is
that your one-and-only transaction can't be read-only and read-write at the
same time. The default DML methods of the TTable or TQuery need a
read-write transaction. So, you build your apps just like you did with
Paradox - a dbgrid (or fancy third-party descendant) over a read-write
TTable or an abominably wide-reaching TQuery and keep it visible all
day. Even if your users do nothing all day but paint their fingernails,
there is no movement of OIT or OAT and garbage collection is stopped.

CommitRetaining is *designed* ...
-- to disguise transactional RDBM systems as Paradox
-- to hide the fact that a transaction is a conversation between a client
API and the server
-- to deliver on Marketing's implicit promise that RAD makes it unnecessary
for the developer to understand how the database engine works
-- is not available to transactional RDBM systems that don't have COMMIT
WITH RETAIN.

With the BDE defaults, a transaction never dies until the connection
dies. IB and Firebird *need* dead transactions in order to clean up. You
*can* take control of TDatabase's transaction, using StartTransaction and
Commit, but a lot of you think it takes the "R" out of
"RAD". Unfortunately, it also takes the "R" out of what you deliver to
your users.

> > We would love to hear how this should be delt with "officially"
> > using Firebird 1.3.972 as the performance and memory eating is
> > somewhat spectacular to watch on a main company server.
>
>Agreed!

"Officially" - that is rib-ticklingly funny. One of the most powerful
themes in this list since the moment it began has been "don't use the BDE"
(or other generic interfaces that hide transactions...) There is a
thriving and competitive industry in our community's third-party satellite
zone that frequently converts our list into a war zone, as to which are the
best direct-to-API Delphi/Kylix/BC++B components for Firebird. We are
*spoiled* for choices.

"Officially", drop the BDE, target "Real Application Development" and get
your head around multi-user concurrency. Then the relevance of
record-versioning and garbage collection in Firebird's implementation of
MUC will all make sense and you'll stop blaming the database architecture
for your spectacular balls-ups.

/heLen


/helen