On 22 August 2013, patrick keshishian <pkesh...@gmail.com> wrote:
> On 8/22/13, Liviu Daia <liviu.d...@romednet.com> wrote:
> > On 22 August 2013, patrick keshishian <pkesh...@gmail.com> 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:
Don't forget to take it out after you're done populating the
database. It doesn't buy you all that much for "normal" operations, and
it greatly increases the chances of data corruption.
[...]
> Still hinting at a slowdown between the two snaps/sqlite3 version
> change.
As I said, I don't really know how to answer that. I'd still go
with transactions, as those would take most of the disk thrashing out
of the picture. With a 55 MB database, it's probably fine to put
everything in a single transaction: just put a BEGIN at the beginning,
and a COMMIT at the end.
The other thing that comes to mind as potentially relevant are
locales, both inside and outside sqlite. For the database encoding:
PRAGMA encoding = "UTF-8"
(or whatever is appropriate for your database).
I'd also compile both versions of sqlite on the same machine, and
do some profiling. If you still can't get an idea what going on from
comparing profile traces, you should probably ask on a sqlite forum...
Regards,
Liviu Daia
