On Sat, 21 Mar 2009 23:42:30 +0100, Stefan Evert wrote: > On 21 Mar 2009, at 15:31, P Kishor wrote: > >> I did some benchmarking with the above schema using Perl DBI, and I >> get about 30 transactions per second as long as I returning the data >> to memory. > > Even for Perl/DBI, that seems pretty slow. Depends on how much data > each of these transactions returns, though -- if there are thousands > of rows in lc or dist for each cell_id, then you can't expect much > better performance. Even though DBI and DBD::SQLite are written in C, > they have to allocate fairly complex data structures to return the > data (in the best case, an anonymous array with 40 to 60 entries for > each data row in the result set), and these operations are relatively > expensive in Perl (I know because I've written some XS code recently > that does this kind of thing).
Perl DBI doesn't do this though. It allocates one array and re-uses it for every row. This makes it very fast. The perl DBI test suite does some benchmarking. Last time I built DBI it could do easily over 10k result rows per second. > Another thing to keep in mind is that the SQLite version included in > the DBD::SQLite distribution is fairly old (3.4.0 on my Mac -- I doubt > there's a more recent version of DBD::SQLite around), and AFAIK there > have been some speed improvements in SQLite recently. There should be a way to update it. Either use DBD::SQLite::Amalgamation from CPAN and update the amalgamation file, or run the getsqlite.pl script before compiling (not sure if that works any more though - it's fairly picky about how the tarball is built). > (Darren, any news from the maintainer of DBD::SQLite? I would be very > delighted and grateful to be able to use an up-to-date SQLite version > in my Perl scripts.) I'm here, I'm just very slow. I did try and pass it off to someone else as maintainer but they vanished. No idea why. Matt. ______________________________________________________________________ This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email ______________________________________________________________________ _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
