Optimal number of "inserts" in a transaction.

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Optimal number of "inserts" in a transaction.

Tal Tabakman-2
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
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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Optimal number of "inserts" in a transaction.

Roger Binns
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 09/01/12 11:28, Tal Tabakman wrote:
> my question is: is there a rule of thumb regarding the optimal number
> of inserts in a transaction ?

How many can you afford to lose should there be an unexpected power
failure before performing a commit?

Other than that you will be building up a journal/wal file of
approximately the same size as the size of the data inserted.  You are
better off picking how large you are happy for that to grow to.  Remember
that you also need to include index sizes if you use them.  In your
circumstances I'd probably pick 128KB or 1MB of outstanding data unless
inserting gigabytes, in which case I would disable all journalling so
transactions wouldn't really matter.

Roger
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAk8LWokACgkQmOOfHg372QR7JwCgq+vF32zL3BlpJGOL+iSnLn7G
L7EAn0ceBvrxJg819uwLZUKvHhlKfNsd
=FHfq
-----END PGP SIGNATURE-----
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Optimal number of "inserts" in a transaction.

Simon Slavin-3
In reply to this post by Tal Tabakman-2

On 9 Jan 2012, at 7:28pm, Tal Tabakman wrote:

> 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
> my question is: is there a rule of thumb regarding the optimal number of
> inserts in a transaction ?

Transactions aren't tricks for speed.  They're a part of data-handling.  A transaction is a bunch of operations on a database which go together: if one is to be done, then they're all done, if one fails, then you wouldn't want the others to be executed because they'd be invalid.

> 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 ?

Can't tell until you try it, with the amount of data in your rows, on your hardware running under your operating system.  But generally it's not important that your application be as fast as possible, only that it be fast enough.  So try your application with all those inserts in one big transaction.  Is it acceptably fast ?  If so, don't worry about it.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Optimal number of "inserts" in a transaction.

Richard Hipp-3
In reply to this post by Tal Tabakman-2
On Mon, Jan 9, 2012 at 2:28 PM, Tal Tabakman <[hidden email]> 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
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users