burmair wrote:

>
>> 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.
>

Besides what everyone said, that a transaction (group of changes that
should be atomic) should represent your business requirements to leave
the database in a consistent state (accordingly to your needs).

I would play a different role on this novel...

Try this one:

1.) start a transaction for each individual update and commit it,
measure the time
2.) start a transaction in the beginning, do all your million updates,
commit at the end, measure the time
3.) start a transaction for a group of changes (let's say for every 5k
changes), commit and start a new transaction until the million updates
finishes, measure the time
4.) Measure the difference between the execution speed of each approach
5.) repeat it some times to get a "good" average
6.) choose your preferable model

From what you said, I can only imagine that, from the consistency point
of view, all your changes (be it one or one million) should belong to a
single transaction. Options 1 and 2 could leave your database in a
inconsistent state in the same manner, if this is acceptable for you, I
think option 2 would give you the best performance. IMO the option 3 is
the only one that is really "safe" and the only one that should be used,
it's up to you to choose.

Let's suppose when you update the single "spreadsheet cell" it triggers
an "after update trigger" that changes another cell, that triggers that
"after update trigger" again and so on. A single update would trigger
the million updates in a bunch of cells and all would be in the same
transaction. That's how Firebird works, the fact that the business logic
that handle the propagation is on the client side or server side should
not interfere in how the transaction is treated. Or it have a total
success or a total failure, anything in the middle is a broken design IMO.

see you !

--
Alexandre Benson Smith
Development
THOR Software e Comercial Ltda
Santo Andre - Sao Paulo - Brazil
www.thorsoftware.com.br