All geek questions in one place

Enabling SQlite3 Faster Using the Perl API

I have DBD :: SQLite to insert some data into SQlite3 db using perl.

I noticed that inserting (pasting 35k rows) takes a lot of time. Is there any way to do this faster.

Optimization is important to me, not data synchronization. How can I optimize it with perl?

Please, help.

+4
source share
4 answers

Try this statement before executing your insertions:

PRAGMA synchronous = OFF

See the SQLite documentation for details.

Also, as Ilion notes, try the prepare() statement only once, and then re- execute() several times with different binding values. Disabling AutoCommit and then explicitly AutoCommit only for all inserted N rows can also help with some N values.

+9
source

Make sure that you use prepared instructions so that you do not analyze each insert. Also try grouping your insertions by surrounding them with Begin ... Commit as described in this FAQ .

+5
source

Make sure AutoCommit => 0 set on your connection. Insteed, fix explicitly after each batch of inserts.

+1
source

Besides automatically disabling auto-commit, you can also try using a large transaction. Makes all the difference for single-line inserts, as a rule.

0
source

Source: https://habr.com/ru/post/1391273/

More articles:


All Articles