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). 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. (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.) > > [1] First retrieve all data from cell table > SELECT * FROM cell WHERE cell_id = :cell_id > > [2] Now retrieve the related lc, dist and met > SELECT lc.* > FROM lc l JOIN cell_lc c on l.lc_id = c.lc_id > WHERE c.cell_id = :cell_id Just a short in the dark, but people on this list have occasionally pointed out that SQLite isn't very good at optimising JOIN queries, so it's often better to use a sub-select clause. (SQLite isn't very good at optimisation in general, at least the version 3.4.0 that comes with Perl, and I've found that sometimes it helps a lot to give a few subtle hints to the optimiser ...) Have you tried rephrasing queries [2] and [3] in this form? > SELECT * FROM lc WHERE lc_id IN (SELECT lc_id FROM cell_lc WHERE > cell_id = :cell_id) I've had good experiences with this approach, although my definition of good performance is rather along the lines of "completes within less than 5 seconds". :-) Best regards, Stefan Evert [ stefan.ev...@uos.de | http://purl.org/stefan.evert ] _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
