Transactions and performance

If I am doing a bulk insert, I keep one transaction open until the
inserts are complete.  I was wondering if there any performance reasons
to commit occasionally.  E.g., say I am inserting 1-million rows into a
table.  Should I commit every 100k rows, or just keep the one
transaction and commit at the end. I was curious if keeping this
transaction open for so many rows would cause any memory concerns or DB
performance concerns.

Also, during this bulk inserting, there are no other transactions open
on the table.



-- 

Bob wrote:

> If I am doing a bulk insert, I keep one transaction open until the
> inserts are complete.  I was wondering if there any performance
> reasons to commit occasionally.

I have not tested this but it seems to me that you should get better
performance with a single transaction as long as all you are doing is
inserts. You will get even better performance if you use the SET
TRANSACTION NO SAVEPOINT option introduced in IB 7.5 SP1, however, I
don't think any data access components except IBX (possibly) support
this option.

-- 

Bill Todd [TeamB] wrote:

> You will get even better performance if you use the SET
> TRANSACTION NO SAVEPOINT option introduced in IB 7.5 SP1, however,

	Really? I thought that only helped if you were updating the records.

-- 
Craig Stuntz [TeamB] · Vertex Systems Corp. · Columbus, OH
  Delphi/InterBase Weblog : http://blogs.teamb.com/craigstuntz
Everything You Need to Know About InterBase Character Sets:

Craig Stuntz [TeamB] wrote:

> 
> 	Really? I thought that only helped if you were updating the records.

The following is an extract from a post by Charlie.

Implicit savepoints are automatically started by InterBase to guarantee
the
atomicity of an SQL statement. However, the execution performance of
some SQL
statements, stored procedures and triggers can be adversely affected by
the
maintenance of the implicit savepoint as the statement continues to
run. Execution
performance includes both CPU and memory consumption. In particular, a
single
transaction that modifies large amounts of data multiple times under
different
savepoints may experience this performance anomaly. Even transactions
that modify
large sets of data, but not multiple times, may experience a lesser
degree of
performance improvement.

The performance improvement may not be great but a savepoint is
maintained to allow the inserts to be rolled back and this requires
some overhead.

-- 

	Right. I take "modify" as distinct from "create" in
Charlie's post.
There's no implicit savepoint required to roll back an insert, unless
you update, because you only ever get one record version. You can
observe that the version was created by a transaction which has been
rolled back and ignore it.

	Of course, only a test would show for sure, but I guess I just read
what Charlie wrote differently.

-- 
Craig Stuntz [TeamB] · Vertex Systems Corp. · Columbus, OH
  Delphi/InterBase Weblog : http://blogs.teamb.com/craigstuntz
Useful articles about InterBase development: