-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On 11/30/2011 03:41 PM, Pavel Ivanov wrote: >> I do have multiple insertions bounded by BEGIN-COMMIT . I am looking at >> possibilities of making the insertions faster. > > Prepare your statement in the form > > INSERT INTO table_name (val1, val2, ...) VALUES (?1, ?2, ...) > > Then for each row you want to insert you will bind necessary values, > execute statement and reset. With all that wrapped in transaction > there's no way to make insertions any faster.
If you have lots of indexes, then for really big inserts (where the ratio of rows added to rows already existing is high), it might be quicker to drop the indexes, do the inserts, then put them back (especially with the new faster CREATE INDEX). Failing that, if you have a block of inserts to do, sorting them by an indexed column (especially primary key) before insertion might boost throughput a bit by increasing locality of reference in the B-Trees. Aside: I was talking about B-Trees with some colleagues when my six year old daughter (then five, I think, actually) butted in and said they'd learnt about B-Trees in school that day. Sadly, it was some spelling game, rather than the school getting a bit of computer science in early... There are index structures that support faster insertions than B-Trees, at the cost of slightly slower selects: streaming merge trees in particular have come to my attention: http://www.acunu.com/blogs/tom-wilkie/castle-storage-engine-oscon/ Perhaps future versions of SQLite might support some of this crazy stuff :-) ABS - -- Alaric Snell-Pym http://www.snell-pym.org.uk/alaric/ -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.11 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAk7WUZwACgkQRgz/WHNxCGoSkQCcCwZ7WzAPsUKMGnN2ZiTJ5AoB xicAn1sgb60s40XQPlYIXdMNmfRoOBxk =DOWQ -----END PGP SIGNATURE----- _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
