On 22 Mar 2009, at 00:27, P Kishor wrote: > Stefan, you were right about "Depends on how much data" part, but it > applied to the weather data, not the lookup tables for lc (or > landcover) or dist (or disturbance). > > Also, "Even for Perl/DBI" makes it seem there is something wrong with > Perl/DBI... no, no... Perl/DBI is an industry-tested, extremely > capable interface. I wouldn't trade it for anything. The problem was > with my problem, with my data size, with my approach. I have to > correct that. DBD::SQLite/DBD::SQLite::Amalgamation/Perl DBI are the > greatest things since SQLite.
I didn't mean to criticise DBI specifically. I'm doing most of my database work through DBI (with either SQLite or MySQL as a backend) and I'm very happy with its ease of use and stability. However, Perl does introduce a lot of overhead, so it may not be the best choice for high-performance applications. In my simplistic benchmarks, Perl did not achieve more than approx. 400,000 subroutine calls per second (MacBook Pro, 2.5 GHz). If you're reading your 7,300 result rows in a loop with fetchrow_* method calls (or if similar subroutines are called internally when using fetchall_* or selectall_*), then you're already limited to less than 60 queries/s by the function call overhead. If you don't need to access the rows separately, you could try to pack the complete data for each met_id with Storable.pm and store it in a single database blob. I haven't tried that myself, though, so I don't know whether that would be any faster. >> 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. > > I am using DBD::SQLite::Amalgamation with SQLite 3.6.11. I am not sure > there is anything wrong with DBD::SQLite 1.1.4 at all (other than its > use of the older SQLite code, which is easily corrected). One might > get better bang by ensuring that the latest version of DBI is > installed, which Tim Bunce and company are probably always > fine-tuning. DBD::SQLite is fine and works very well for me, but hasn't seen any updates to a newer SQLite version in quite a long time. My understanding is that it's not entirely trivial to replace the SQLite source code (without understanding all the XS interfacing code), and that it would be better and easier to use the amalgamation anyway. I'd love to switch to DBD::SQLite::Amalgamation, but I need to register my own callback functions, and this package fails the callback tests with a bus error. Cheers, Stefan _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
