Re: [pysqlite] :memory db

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Dinesh B Vadhia wrote:
> Is there a good source on how to create an in-memory pysqlite db? 

Name the database :memory:

> Specifically, do you create a new :memory db and load the data into the
> db 

You can copy the data at the SQL level.  Use attach.  eg

 con=...(":memory:")
 con.execute("attach 'diskdb' as diskdb")
 # repeat next line for each table
 con.execute("create table foo as select * from diskdb.foo")

This requires copying across each table, recreating triggers etc.

> or can you load an existing sqlite db into memory?

You can't do that with pysqlite, but you can do it with the sqlite
backup apis which copy the underlying database pages if using APSW:

  http://apsw.googlecode.com/svn/publish/backup.html

> Our db schema is simple but the db contains > 4m records and will be run
> on a 64-bit OS with 6 to 8gb of available ram.  The reason for wanting
> to use a :memory db is purely performance.

You probably don't need to use a :memory: database anyway.  SQLite
itself has a per connection cache whose size you can alter using pragmas:

  http://apsw.googlecode.com/svn/publish/backup.html

Your operating system also has a file cache.  You can pre-fill the file
cache by copying the database to nul or /dev/null. Increasing the SQLite
page size may also help.  On Linux you can have a memory+swap based
filesystem using tmpfs.

The first thing you should do is have some sort of representative
performance test and then try the various mechanisms to see what is most
effective.

Roger
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (GNU/Linux)

iEYEARECAAYFAkpMPzsACgkQmOOfHg372QSatQCcCua1SoYBiSMmCj33EifSBXQp
ayUAnjCLOPUakGV9D9gvkmYhNAS+wMA+
=4SaE
-----END PGP SIGNATURE-----