> > ...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?...
> >
> > Use a transaction per unit of work. Period.
>
> What constitutes a unit of work? You could think of my application as
> a REALLY big spreadhsheet, with Firebird as the persistence layer.
> Make a change in a single cell, and the effects ripple through the
> entire spreadsheet. Is the unit of work a single cell update?
> Perhaps first generation effects are a unit of work, then second
> generation, and so on. There are other plausible groupings in our
> multi-dimensional models, but it's not always easy to find the
> boundaries. Maybe the entire update is a unit of work (this seems to
> me the most sensible interpretation), but then the question becomes,
> how many millions of DB updates can reasonably be performed in a
> single transaction?
>
> I guess my question really is, how much overhead is there in the setup
> and teardown of a transaction, and will that overhead be affected by
> any of the DB parameters that I can manipulate?

A unit of work is what you want to have saved in the database so
that no information is lost between the different saves. You should
be able to go from one consistent state of saved data to another
by performing calculations/adding new data/whatever.

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle &
MS SQL Server
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com