[yunqa.de] Re: DISQLite: disadvantage to using a transaction?
- From: Delphi Inspiration <delphi@xxxxxxxx>
- To: yunqa@xxxxxxxxxxxxx
- Date: Wed, 21 Nov 2007 13:09:34 +0100
>Now for the stupid question :) Using a database transaction increases insert
>and update performance hugely.
Correct.
>I also notice that I can read the updated data (via SELECT) even before it has
>been committed.
True as well.
>So, the stupid question is, what is the disadvantage (in my situation) to
>starting a transaction as soon as I've created the database? Surely there must
>be a drawback somewhere?
The bottleneck of INSERT performance is the implied COMMIT that occurs after
each INSERT if you do not have an explicit transaction. Because DISQLite3
transactions are ACID, the COMMIT does not return until all data has been
safely written to the disk platter. This typically takes two complete rotations
of the disk platter, which means you can do no more than about 60 COMMITs per
second on your average computer.
Within a transaction (in other words, without the implicit COMMIT after each
INSERT), you should be able to do 60000 INSERTs/second and more.
By issuing a transaction you basically avoid the time-consuming implicit
COMMIT. All data is then written directly to disk without further checks. This
certainly makes INSERTS a lot faster, but the downside is that the data does
not necessarly reach the disk surface when DISQLite3 thinks it does, and so if
you lose power, your data might get corrupted.
So the drawback with larger transitions (where much data is INSERTed or
UPDATEed) is that more data might get lost in case of power failure. Therefore
commit after each modification for maximum safety.
However, if your tables are temporary only anyway, I assume that this does not
truely apply to you.
>Is SELECT slower (I'm just using the RowID) or could it return the wrong
>results? Is memory usage going to be higher?
None of either.
>If relevant, for PRAGMA I've got locking_mode=EXCLUSIVE and synchronous=OFF
Good choices.
Ralf
_______________________________________________
Delphi Inspiration mailing list
yunqa@xxxxxxxxxxxxx
//www.freelists.org/list/yunqa
Other related posts:
