python, sqlite3: Load existing db file to memory

Question

Hi,

I have an existing sqlite3 db file, on which I need to make some extensive calculations. Doing the calculations from the file is painfully slow, and as the file is not large (~10 MB), so there should be no problem to load it into memory.

Is there a Pythonic way to load the existing file into memory in order to speed up the calculations?

Thanks,

Adam

Answer 1

sqlite supports in-memory databases.

In python, you would use a :memory: database name for that.

Perhaps you could open two databases (one from the file, an empty one in-memory), migrate everything from the file database into memory, then use the in-memory database further to do calculations.

Answer 2

sqlite3.Connection.iterdump "[r]eturns an iterator to dump the database in an SQL text format. Useful when saving an in-memory database for later restoration. This function provides the same capabilities as the .dump command in the sqlite3 shell."

Answer 3

First you should try and find out what is causing the slowness you are observing. Are you writing to tables? Are your writes within large enough transactions so that you don't save needless temporary results to disk? Can you change writes to go to temporary tables (with pragma temp_store=memory)? Can you live with pragma synchronous=off?

I don't think this functionality is exposed in the Python module, but sqlite has a backup API that sounds like exactly what you are asking for: a way to copy from one database to another (either one of which may be an in-memory database) that works pretty much automatically without any user-visible enumeration of tables. (Maybe APSW exposes this?)

Another option is to create a ram disk (if you have sufficient control of the environment) and copy the file there.

Answer 4

This has already been answered before, including code examples at http://stackoverflow.com/questions/3826552

You do not mention operating system, but one gotcha of Windows XP is that it defaults to a 10MB file cache, no matter how much memory you have. (This made sense in the days when systems came with 64MB etc). This message has several links:

http://marc.info/?l=sqlite-users&m=116743785223905&w=2