[EMAIL PROTECTED] wrote:
i got a question concerning in-memory-behaviour of sqlite.
this is what i'm trying to do:
i load an existing file db into a memory one (create a :memory:-connection,
attaching the file db which has ~ 60 mb and then copying its tables and indices
and detaching the file db - this works quite great so far, lasts around 5
secs). now i begin a transaction on the in-memory-db and start executing my
queries (some 10k inserts, updates and deletes). and this process is horribly
(sic!) slow.
in comparison i did all the inserts into an empty memory-db (without cloning
the whole file db first) which is faster by around factor 1000...
anyone has a clue what happens there? i switched off windows virtual memory
paging - the same effect. disk io is near zero, it's really the db commands
only that causes this huge difference... working on the file db is much faster
than on memory given the same process. do i have to pragma sth special (tried
almost everything here, too...)
In tests I have done I found that the :memory: databases are slightly
slower than file based databases for exactly the same operations, as
long as the database size is fairly small. I believe this is true as
long as the entire file database fits in the OS disk cache. I suspect
the time difference is due to the OS cache paging code, which is highly
optimized, being slightly faster than SQLite's memory paging code.
You are seeing a factor of 1000 difference between inserts into an
*empty* file database and a 60 MB memory database when doing 10K
inserts, updates, and deletes. That is probably almost entirely due to
the extra time it takes to modify indexes on the database tables after
the database is populated. You need to compare the time to these inserts
into an empty file database wiith the time it takes to do the same
inserts into an empty memory database.
If these times are similar as I suspect, then you might want to try
dropping your indexes (if they aren't needed for doingthe inserts)
before the inserts, inserting the new data, and then re-creating the
indexes after all the new data is added.
HTH
Dennis Cote
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
