On 22 August 2013, patrick keshishian <[email protected]> wrote:
> Hi,
>
> Anyone else notice that sqlite3 in base got slower somewhat recently?
>
> I have a fairly large database, roughly 55M in size. I had to
> repopulate it from source SQL file recently, and it took more than
> twice the time I remember it taking with an older snapshot.
>
> This is on "newer" snapshot[1]:
> $ time sqlite3 the.db < in.sql
> 50m13.15s real 3m57.25s user 8m15.78s system
[...]
I recently had to populate a SQLite database with ~500k records, the
end result being a ~240 MB file. I can't answer your question about
sqlite3 getting slower, but I can tell you that tuning operations makes
a huge difference. I suggest something along these lines:
(1) set some pragmas:
PRAGMA synchronous = OFF
PRAGMA temp_store = MEMORY
PRAGMA journal_mode = MEMORY
PRAGMA page_size = 65536
(2) use transactions and commit every 10k inserts (or more), rather than
after each new record (which is the default);
(3) drop all indices, push the data, then re-create indices.
Each of these have dramatic effects on speed. Other optimisations
are possible too, but I believe these are the important ones. In my
case, I cut database creation time from more than an hour to 80 seconds,
on a relatively slow machine. FWIW.
Regards,
Liviu Daia
