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

Thanks for the reply Gerhard and for looking at the code. I did another test
comparing cPickle to pytables (www.pytables.org). I¹d still prefer to use a
relational database like sqlite but pytables, which does allow some
selection as well, is almost as fast as cPickle (see below). As Francsesc
suggests in his email (link below) this may be because pytables uses numpy
containers.

Best,

Vincent

http://permalink.gmane.org/gmane.comp.python.numeric.general/16221


Test saving recarray using cPickle: 1.620880 sec/pass Test saving recarray
with pytables: 2.074591 sec/pass Test loading recarray using cPickle:
1.023015 sec/pass Test loading recarray with pytables: 0.882411 sec/pass
http://permalink.gmane.org/gmane.comp.python.numeric.general/16221


On 7/21/07 6:03 AM, "Gerhard Häring" <[email protected]> wrote:

> -----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-----
> _______________________________________________
> pysqlite mailing list
> pysqlite-IAPFreCvJWPBWskQ1e/[email protected]
> http://lists.initd.org/mailman/listinfo/pysqlite
>

_______________________________________________
pysqlite mailing list
pysqlite-IAPFreCvJWPBWskQ1e/[email protected]
http://lists.initd.org/mailman/listinfo/pysqlite