Faster Inserts and updates with CFTRANSACTION

If you are ever doing lots of inserts of updates, you can speed up the processing
by a substantial factor by wrapping up lots of individual records ( ie loop over and update a dataset ) in a CFTRANSACTION statement.

This actually applies to any programming language and any decent DB .

Why? Each query when it is finished will be explicitly committed (talking CF specifically here, btw).

In oracle that means write to disk (generally the same for other databases). So for 1000's updates that's a 1000 disk writes where oracle is writing the changes out to disk. That's a lot of overhead.

When you wrap things up in a transaction, that disk activity is postponed until a commit occurs, either explicitly <cftransaction action="COMMIT"> or when it's finished </cftransaction>

Depending on how your DB is configured in terms of REDO logs etc, what your inserting or updating, the optimal size of a transaction differs.

How to know how many rows to process before issuing <cftransaction action="COMMIT">? It's a bit of a black art :)

There are some cool tricks in Oracle like Asynchronous Commits which effectively says to Oracle, you know better than me, you decide when to commit.