David Van Couvering wrote:

> I am running fairly simple transactions.
>
> TPC-B has a fairly simple schema. There are three tables: accounts,
> branches and tellers.
>
> ACCOUNTS(accountid integer, branchid integer, accountbalance integer,
> junk char(88), primary key(accountid))
>
> TELLERS(tellerid integer, branchid integer, tellerbalance integer,
> junk char(88), primary key(tellerid))
>
> BRANCHES(branchid integer, branchbalance integer, primary key(branchid)
>
> I am running with 1 branch, 10 tellers, and 100,000 accounts
>
> I am running with 20 clients, and am getting 20TPS. With 1 million
> accounts I was getting 10TPS. I upped the cache size using gfix to
> 10,000 and got 5TPS.
>
> They are all prepared, but I am noticing that it is prepared each time
> prior to execution. I can fix that, but I think my point is that I am
> running exactly the same code on Pointbase, which is running at about
> 120 TPS,, and on MySQL which is running at over 1000 TPS. So Firebird
> is just not comparing well. Are repreparing statements in Firebird
> significantly costly?
>
> David

Yes. preparing a statement is the single most expensive operation in Interbase/Firebird.
The 2nd most costly operation is starting a transaction and commiting.

If you use a single paramaterized query and simply change your parameters
each time your performance would increase by at least an order of magnitude
probably 2 orders of magnitude.

Also, if you can bundle your transactions into single larger batches (i.e. do
100 - 500 operations and then commit a single time) you would get another drastic performance
increase.

Also, you may want to play with server-side autocommit, if you aren't willing to take
explicit control of start and end of transactions. Although I haven't ever played with this
as my client side libraries only recently started supporting this and I always explicitly
control my transactions any way.

Also make sure you are not using COMMIT-RETAINING, unless you need it, and you
know why you need it.

Commit retaining is a way to commit a transaction, while keeping the previous context alive
ensuring that any values you have previously looked at are still valid. However, it also
means that your transaction continues to keep those old values "interesting" and will
prevent garbage collecting them (if they have been over written by someone else).

Next to the cost of repreparing your statement over and over again, almost all other
possible sources of performance problems are pretty insignificant.

DZ