Hi

>> I have a large application that used to be based on DBase. During
>> conversion to FB we used the AutoCommit functionality to avoid having to
>> re code large parts of the application.
> Autocommit is often the lazy way out for conversions from old desktop database applications. Its actual usefulness is where you have a repeating task of multiple steps, where just parameters change from one (database) transaction to another. Efficient implementation wraps the steps of the task in one transaction. In theory, Autocommit should be a good choice for a POS system task since the tasks are intended to complete quickly. At 115,000 (database) transactions a day I'd wonder

Yes it was the lazy way out and seemed to work great!

> (a) whether your app is overdoing the commits where it should be committing only on completion of the task (happens if you've chosen to go down the old Borland path and totally ignore database transactions)

Yes. Lots of areas ignore transactions and rely on auto commit. The POS
function starts an explicit transaction, which I understand does a hard
commit on any existing CommitRetaining transaction save points (or
whatever they are called). Processes the POS sale and does a commit (or
rollback on error).

> and
> (b) whether you have checked to ensure none of your transactions is using CommitRetaining (also an old Borland ploy to make Paradox and dBase programmers OK with ignoring the fact that they've moved from a desktop, file-driven data storage system to a multi-generational, server-based data management system)

Yes. Most of the code does this. When transactions have not been
explicitly added, the components connect to the IBODatabase without
specifying a transaction. they get the default transaction which is set
to AutoCommit and CommitRetaining (the defaults).

> (c) whether you are using TIBOTables (aaaarrrgh!!)

Yes. Also using tables. A triple whammy (a, b and c) !!. Mainly for
old code, most new code uses IBOQuery. If I disable CommitRetaining
what impact will that have on my code, will it help and what code will I
need to change? Please excuse my ignorance. My understanding is that if
I disable CommitRetaining I will get a hard commit after every Post in
the example below. Could I improve it by leaving commitRetaining on and
at the end forcing a hard commit?

i.e if my code is

Table1.Edit;
Table1.FieldByname('d').AsString := '2';
Table1.Post;


Table2.Edit;
Table2.FieldByName('val').AsString :=
Table1.FieldByname('someval').AsString;
Table2.Post

>
> On the other hand, that might be one old transaction that you started on Day 5 (such as a query on the monitoring tables) that you can easily remedy.

I have already checked for this and am pretty sure I have sorted any
transactions like this. As an asside, am I correct in saying that
having a long running read only transactions isn't a problem?

>> I was doing some stability
>> tests by simply processing a POS (Point of Sale) process repeatedly
>> (1million times). I noticed that the time to process each POS
>> transaction slowly increased. I did a GStat and noticed the big gap
>> between 'Next Transaction' and the Oldest transaction entries and
>> thought this could be the culprit!
> The cache size you have set here is absurd - this is Superclassic, right? SC does not have a global cache like Superserver. You have an 8KB page size so 20000 * 8 = 160,000 KB (about 156 MB) of server RAM being reserved for *each* of those attachments. For an 8KB page size with reasonable RAM resources on the host server, think in the low HUNDREDS of pages for the caches, e.g., 128, not TWENTY THOUSAND! That's not an IBO issue - unless everyone is logging in as SYSDBA and setting page buffers from the application - ouch! Check that this isn't so.

The cache size was just for testing. I have around 3 connections on my
test DB and 16GB ram. Im not sure what you meaning by 'That's not an
IBO issue - unless everyone is logging in as SYSDBA and setting page
buffers from the application - ouch! Check that this isn't so.'

Our connections are SYSDBA. I thought the buffers was set only in the
DB and that IBO didn't do anything with it?

> So, yeah, this looks a bit like an iceberg. Suspect CommitRetaining and/or long-running read/write transactions, wherein a close examination of your component properties could be game-changing.

Yes, you are 100% right. I need to make changes to fix this up. There
is a lot of code to work through and we don't have plans to rewrite all
the old code! What I am looking for is a way to reduce the burden on
FB. Obviously CommitRetaining is the issue, I just need to better
understand the effects of this and the alternatives. If I could keep
autocommit and disable CommitRetaining I suspect that would help.

Really appreciate your help so far Helen :)

Cheers
Rob