[odb-users] SQLIte "INSERT" slows dramatically...

[Andrew Cunningham]

Actually, I **just** tracked down the problem. It was my fault, but the
symptoms are interesting.

I am using SQLite with "deferred" foreign keys.
My data structure is a medium number (100's) of subsystems each pointing to
"thousands" of elements.
A small number of the elements may also point to an auxiliary object ( this
object is typically NULL in the element data structure).

The sudden SQLite  performance degradation happened when one of my
"elements"  was persisted but was pointing to a non-persistent "auxiliary"
object.

This is a violation of a foreign key constraint,and would have caused an
exception in immediate mode, or on "commit" in deferred mode -  but I never
got to that commit stage as it would have taken 24 hours at least.

The key point I want to make here is that although this foreign key
constraint was violated for only a handful of "elements" the performance of
SQLite was degraded 1000x from that point on.




On Mon, Sep 14, 2015 at 11:36 AM, Boris Kolpackov <boris at codesynthesis.com>
wrote:

> Hi Andrew,
>
> These two pages list various tuning options for using SQLite with
> very large databases. In particular, I would try the WAL journal.
>
>
> http://stackoverflow.com/questions/10209438/sqlite-end-transaction-is-taking-too-long
>
> http://stackoverflow.com/a/6533930
>
> Boris
>