Re: [Modeling-users] SQLite inserts slow

[Sebastien B. <sbi...@us...>]

Ernesto Revilla <er...@si...> wrote:
> Dear S=E9bastien and everybody else:
>=20
> I found SQLite inserts a bit slow. When enabling logging, I found that new
> keys are retrieved using the following SQL statement:
>=20
> Transaction: BEGIN
> Evaluating: UPDATE PK_SEQ_ROOM SET id=3D((select max(id) from PK_SEQ_ROOM=
)+1)
> Evaluating: select id FROM PK_SEQ_ROOM
> Transaction: COMMIT
>=20
> Then, after checking the table definition I saw that  PK_SEQ_ROOM has just
> the id field, and as I read anywhere below, just one row, so the subquery
> should not be needed, not?
>=20
> (file SQLiteAdaptorChannel.py near line 82)

Well, right: in fact, since sqlite db is not mt-safe at all, the
subquery has no interest (and BTW the second one should read SELECT
LAST_INSERT_ID() in mysql e.g., I need to change that).

> On the other hand, changing this does not make it much faster. at least w=
ith
> my box, committing on each update and read for new primary keys is a bit
> slow, but for this delay sqlite has to be blamed.

Probably the pb. for you here is that every single new object fetches
its own PK; yes, it would be really more efficient to fetch the <n> next
PK if we need <n> new values... Could you fill a RFE so that I do not
forget?

> Interesting enough that the following instruction makes it much faster
> causing SQLite to turn off disk sync:
> *PRAGMA default_synchronous =3D OFF;

Yes, but this makes the sqlite-db file very fragile, I believe this can
completely waste your data when some errors happen, or am I wrong? (no
time to check that in details right now)

-- S=E9bastien.