John, Richard, A quick test shows the following:
1. Creating the database ?normally? without using WITHOUT ROWID. This has 1703538 rows. Doing 1,000 randomish accesses via the web service takes 10 secs. 2. Using a perl assoc array and doing the same access via a web service takes 10 secs. This also has 1703538 rows. 3. We cannot get WITHOUT ROWID working using Perl DBI. We get the following error DBD::SQLite::db prepare failed: malformed database schema (postcode) - near ?WITHOUT" This appears to be due to mismatched SQLite version but we are running macpro:postcode rwillett$ sqlite3 --version 3.8.5 2014-08-15 22:37:57 c8ade949d4a2eb3bba4702a4a0e17b405e9b6ace The SQLite web page on WITHOUT ROWID indicates we need 3.8.2 or higher so we?re confused as to what the problem is. We?ve never actually had problem with SQLite and Perl DBI so we need to look into it a little more. 4. We then ran the same test using nothing more than select statements directly into sqlite3 e.g. select * from postcode where id = 'BT635SW'; select * from postcode where id = 'BT350PD'; select * from postcode where id = 'BT667TG'; select * from postcode where id = 'BT750PE'; select * from postcode where id = 'BT621AE'; select * from postcode where id = 'BT623PJ'; select * from postcode where id = 'BT670BX'; select * from postcode where id = 'BT623EG'; select * from postcode where id = 'BT670DS'; select * from postcode where id = 'BT655BU?; ?? We ran 1,000 tests in real 0m0.025s user 0m0.013s sys 0m0.013s So the ?direct? connection runs approx 400x time quicker. Now this direct connection doesn?t have to open and close the database each time as ours currently does and doesn?t have the overhead of the web service around it. So its not a realistic comparison, but it does show how quick sqlite can be :) In conclusion 1. I can optimise the actual SQL as much as Iike, the bottleneck isn?t SQLite at the moment. 2. I need to work out what is happening using WITHOUT ROWID. We?ll dig further but suspect that we are now optimising the wrong thing in our system :) Rob If we ignore the WITHOUT ROWID issue, then > On 17 Sep 2015, at 14:21, John McKown <john.archie.mckown at gmail.com> wrote: > > On Thu, Sep 17, 2015 at 7:21 AM, Rob Willett <rob.sqlite at robertwillett.com> > wrote: > >> John, >> >> Thanks for this. >> >> The base OS is Linux on a a 64bit server, the wrapper will be Perl running >> as a web service under Mojolicious. It will eventually be presented as a >> web service. >> >> We could simply load up the entire database into Perl into an Assoc array >> and search on that. >> > > ?This is probably would I would do, in this particular case.? > > > >> >> Richard, >> >> WITHOUT ROWID was what I was looking for. Thanks > > > ?I'm going to have to review this option as well. I'm not familiar with > what it accomplishes, performance wise.? > > > >> . >> >> All, >> >> What we?ll do is write a quick Perl script to check the speed of an assoc >> array in Perl vs SQLite. It might be instructive to see the difference. >> > > ?Yes, it would. Please post your results. I'm really curious about it.? > > > >> >> Rob. >> >> > > -- > > Schrodinger's backup: The condition of any backup is unknown until a > restore is attempted. > > Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be. > > He's about as useful as a wax frying pan. > > 10 to the 12th power microphones = 1 Megaphone > > Maranatha! <>< > John McKown > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
