OK, thanks, I'll try that, great tip!

David

David Zvekic wrote:

> 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
>
>
> *Yahoo! Groups Sponsor*
> ADVERTISEMENT
> <http://rd.yahoo.com/M=246920.2960106.4328965.1728375/D=egroupweb/S=1705115386:HM/A=1513703/R=0/*http://www.gotomypc.com/u/tr/yh/cpm/grp/300_06F/g22lp?Target=mm/g22lp.tmpl>
>
>
>
> To unsubscribe from this group, send an email to:
> ib-support-unsubscribe@egroups.com
>
>
>
> Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service
> <http://docs.yahoo.com/info/terms/>.