On Mon, Jan 9, 2012 at 2:28 PM, Tal Tabakman <tal.tabak...@gmail.com> wrote:
> Hi guys,
> I have an application that perform a lot of insert operations to a table
> (each raw contains one ~20 character string and 10 integers)
> I have read a bit about insert performance and use transactions for my
> insert operations
>
If you do:
PRAGMA journal_mode=WAL;
PRAGMA synchronous=NORMAL;
Then the performance will probably be about the same regardless of your
transaction size, and will be probably be faster than anything you can
obtain with the default DELETE journal_mode. Try this and see if it works
fast enough for you.
Note that with PRAGMA synchronous=NORMAL and journal_mode=WAL if you
suddenly loss power, then one or more recently committed transactions might
get rolled back. In other words, you lose Durability, the "D" in "ACID".
The database won't go corrupt (unless there are other unrelated problems
with your OS and/or hardware) but you might lose the last few seconds of
work. Most people don't care about this, but you'll need to make that
determination for yourself based on your requirements.
> my question is: is there a rule of thumb regarding the optimal number of
> inserts in a transaction ?
> i.e. suppose that all in all I have 1000000 inserts operations, what will
> be faster, to bundle 20 transactions of 50000 inserts or 10 transactions of
> 100000 inserts ?
> cheers
> Tal
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
--
D. Richard Hipp
d...@sqlite.org
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
