--- In firebird-support@yahoogroups.com, "Timothy Madden"
<terminatorul@...> wrote:

>
> On Thu, Sep 4, 2008 at 4:16 PM, burmair <YahooSpam@...> wrote:
> > --- In firebird-support@yahoogroups.com, "Martijn Tonies"
> > <m.tonies@> wrote:
> >>
> >> ...In terms of performance, is it faster to do as much as

possible in a

> >
> >> > single transaction (or maybe a few "chunks"), or is it

essentially the

> >> > same if each update is performed in its own transaction?...
> >>
> > ...You could think of my application as
> > a REALLY big spreadhsheet, with Firebird as the persistence layer.

...

> It looks obvious to me that you need to do all the updates in a

transaction.

Theoretically yes, but practical issues need to be addressed.

>
> Should your application be interrupted (by a bug, a crash of some

kind or

> a power failure, whatever ...) you would not want half of the cells

updated

> and half old. What kind of app are you doing that requires millions of
> inter-dependent updates when a single cell changes?

As I described above, a REALLY big spreadsheet, where an update to one
cell causes a cascade of changes through many other cells. It's not
actually a spreadsheet, of course, but the analogy works very well.
It's really a tool that can model the interaction of large numbers of
independent entities, be they consumers in a marketplace, stars in
colliding galaxies, or fissioning atomic nuclei.

> Maybe that is not
> a good design to begin with.

Perhaps not, but it's the best we could come up with.

> I do not know about transaction overhead, or how many updates can be
> pushed into one, but you should not worry too much about it, until you
> can actually observe performance penalties.

You can assume we have performance issues.

> If you use too many
> transactions, on the other hand, instead of grouping more statements in
> a single transaction, you might observe penalties because of the
> forced writes to the disc. Transactions need to be durable, that is

every

> time you commit you get a disc access. Make sure you do that only
> as often as it is necessary.

I haven't tested it, but I suspect that's incorrect. Writes should be
occurring to the disk throughout the transaction, not just at
transaction boundaries, as candidate records are placed in the DB in
anticipation of the commit.

It would be useful to know how transactions, caching, and forced
writes interact. Within the context of a transaction, it's
conceivable that a cached Firebird page could be modified by an
update, but not actually written to disk until some event occurs, like
a timer, a threshold flush, or the commit. If Firebird even caches
writes. Then there's the question of forced writes, and how they
might affect the process.

I think I can reduce this to a simple question: in general, is a
single large transaction or a lot of smaller transactions
significantly (or even noticeably) faster, and if so, what Firebird
parameters might be relevant?