Re: Pickle and sqlite - loading and saving recarray's

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

Vincent Nijs wrote:
> I posted the message below on the numpy discussion list. Few if any people
> there seem to use sqlite.
> 
> I am interesting in using sqlite to store data for scientific research. I
> wrote the attached test program to save and load a simulated 11x500,000
> recarray (a common array type in Numpy). Average save and load times are
> given below (timeit with 20 repetitions). The save time for sqlite is not
> really fair because I have to delete the data table each time before I
> create the new one. It is still pretty slow in comparison. Loading the
> recarray from sqlite is significantly slower than cPickle. I am hoping there
> may be more efficient ways to save and load recarray¹s from/to sqlite than
> what I am now doing. Note that I infer the variable names and types from the
> data rather than specifying them manually.
> 
> saving recarray with cPickle:       1.448568 sec/pass
> saving recarray with sqlite:         193.286204 sec/pass
> 
> loading recarray using cPickle:    0.471365 sec/pass
> loading recarray with sqlite:        15.977018 sec/pass
> [...]

I didn't see anything that could really improve performance. The pysqlite
2.3.5 performance tuning does not seem to make any difference here. By
using apsw, you can get about twice as fast in the executemany, but you're
still two orders of magnitude slower than cPickle.

> I did get an interesting response on this issues from Francesc Altet
> (designer of pytables) on the numpy-list that might (or might not :) ) be of
> interest to readers of the pysqlite list.
> 
> http://permalink.gmane.org/gmane.comp.python.numeric.general/16221

It's hard to beat a simple serialization like cPickle. But using a RDBMS
will certainly pay off once you *selectively* want to get data out of your
database.

The overhead here are really the Python adapters and SQLite being a
database instead of a file stream that you dump serialized data to. Playing
with SQLite's PRAGMA statements will most probably not make any noticable
difference.

- -- Gerhard
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFGoef0dIO4ozGCH14RAjDcAJsHvt32PE9hiWpIWvi8iYkAMv/S9gCgnin0
pSXIQv8gRAz6kn9BA+kvRuQ=
=dRzT
-----END PGP SIGNATURE-----