Transactions can improve database performance

This seemed counter-intuitive to us when we stumbled upon it, but apparently wrapping update statements inside a transaction can improve the performance of the statement.

We were working on an import script that imports a large set of new data and also updates related records in existing data. The process runs asynchronously so we weren’t concerned too much with performance–it’s a long running process updating 7,000,000+ existing records with related data from 100,000 new ones.

However, we were surprised to notice that the same queries took over 1,000 seconds to run through our application whereas they ran in about 150 seconds in SQL+. After a lot of trial an error we wrapped the application queries in a transaction and the performance improved to be the same as running the statements in SQL+.

Because of the nature of the data load we don’t really need autonomous commits and thus weren’t using transactions to start with. We were very surprised to notice the speed increase after applying transactions.

3 thoughts on “Transactions can improve database performance

Leave a Reply

Your email address will not be published. Required fields are marked *


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>