On 8/22/13, Liviu Daia <[email protected]> wrote:
> 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
Thanks for this info!
Adding only "PRAGMA journal_mode = MEMORY" was a tremendous
help:
$ time sqlite3 the.db < in.sql
journal_mode = memory
3m50.12s real 1m28.79s user 0m44.58s system
That's impressive!
yet, and on the other machine (with older snap):
$ time sqlite3 test.db < in.sql
journal_mode
------------
memory
1m29.85s real 0m50.24s user 0m22.04s system
Still hinting at a slowdown between the two snaps/sqlite3 version
change.
Thanks again for the info!
--patrick
> (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
