Making SQlite3 inserts faster using Perl API

Question

I am DBD::SQLite to insert some data in SQlite3 db using perl.

I have noticed that it takes a lot of time to insert(inserting 35k rows). Is there any way to make it faster.

Optimization is important for me rather than data sync. How can i optimize it using perl?

Please help.

Answer 1

Try executing this statement before doing your inserts:

PRAGMA synchronous = OFF

See the SQLite documentation for more information.

Also, as Ilion notes, try to prepare() the statement just once and then re-execute() multiple times with different bind values. Turning off AutoCommit and then explicitly committing only every N rows inserted may also help, for some values of N.

Answer 2

Make sure you are using prepared statements so it doesn't have to analyze each insert. Also try grouping your insert statements by surrounding them with Begin ... Commit as described in this FAQ.