Re: SQLite inserts slow

Ernesto Revilla <[email protected]> wrote:
> Dear Sébastien and everybody else:
> 
> I found SQLite inserts a bit slow. When enabling logging, I found that new
> keys are retrieved using the following SQL statement:
> 
> Transaction: BEGIN
> Evaluating: UPDATE PK_SEQ_ROOM SET id=((select max(id) from PK_SEQ_ROOM)+1)
> Evaluating: select id FROM PK_SEQ_ROOM
> Transaction: COMMIT
> 
> 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?
> 
> (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 with
> 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 = 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ébastien.



-------------------------------------------------------
This SF.Net email sponsored by Black Hat Briefings & Training.
Attend Black Hat Briefings & Training, Las Vegas July 24-29 - 
digital self defense, top technical experts, no vendor pitches, 
unmatched networking opportunities. Visit www.blackhat.com