Subject Statements incompatibilities/un-affinities in a same transaction
Author Jerome Bouvattier

I'd appreciate any comments on this...

We deploy an application with an IB database. Each month, we send clients an
"update db".
This update db contains
* tables with recent data changes
* a "script table" containing custom actions read by a home made pump

The script execution can lead to
* a fair amount of record addition/update/deletion. (hundreds of K rows if
not millions)
* DDL statements.
* Select statements to decide if a record should be modified/updated/deleted
or not.

Most of the script is executable in the context of single transaction to
keep things consistent.

Currently we use the following guidelines as to what statements should or
shouldn't be part of a same transaction :

1) Never mix DDL & DML.
2) Close and re-open db after DDL.

That's about all.

Recently I discovered a new one :

3) Avoid adding temporary records in a table and delete them after. The
delete execution takes for ever compared to case where a commit is issued
before. The ratio can as bad as 5 minutes/2 secondes.

Questions :

- Do you see other mix I should avoid ?
- What is the explanation for 3) ?
- Are VERY LONG transactions (read trans issuing loads of statements) a bad
idea ?