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.
Richard,
WITHOUT ROWID was what I was looking for. Thanks.
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.
Rob.
> On 17 Sep 2015, at 13:12, John McKown <john.archie.mckown at gmail.com> wrote:
>
> Well, this may be a heretical answer. Given what you have said, I wouldn't
> even try to use SQLite. Well, at least not directly. I would use a "hash
> table". SQLite's indexing, if I understand correctly, is a B-Tree. And that
> is the _only_ option. What would likely work better is a "hash index". You
> might get some more concrete answers if you were to post the OS and
> implementation language. Such as, Windows 8.1 using C#. Or Linux 64-bit
> using C++. Also, do you an SQL data base? Perhaps something else would be
> better, if you're not really doing relational queries. But I don't know
> what, given that I don't know your system environment.
>
> On Thu, Sep 17, 2015 at 6:58 AM, Rob Willett <rob.sqlite at robertwillett.com>
> wrote:
>
>> Hi,
>>
>> There was a topic on here a few weeks ago which I cannot remember and
>> cannot find in the mess that is my e-mail system and after spending the
>> last hours search the SQLite archives I still can?t find it so will ask
>> here if anybody can remember or help. I can?t even format the question for
>> Google to search on :(
>>
>> The specific question I have is about trying to provide the fastest
>> response possible to a select query. I recall that the e-mail talked about
>> using an index to satisfy the query and therefore never having to go out to
>> get the rest of the data from the table, so it was a lot quicker. Is there
>> anything that I need to do specially to make this happen. e.g. if I put all
>> the fields of the table in the index BUT I really only search on the
>> primary key
>>
>> The reason for this I want to look up UK postcodes (Zip codes to our
>> American brethren) and get their longitude and latitude. A UK postcode
>> identifies a number of houses or commercial buildings. Depending on the
>> area it can be just one building (a big one) or if you are in the country
>> it can be quite a big area. If you sent a letter just to a postcode with no
>> other identifier it probably wouldn?t get delivered, but putting a name on
>> it or a building number, there?s a very good chance the post(wo)?man will
>> deliver it.
>>
>> The CSV file looks like this
>>
>> id,postcode,latitude,longitude
>> 1,AB101XG,57.144165160000000,-2.114847768000000
>> 2,AB106RN,57.137879760000000,-2.121486688000000
>> 3,AB107JB,57.124273770000000,-2.127189644000000
>> 4,AB115QN,57.142701090000000,-2.093014619000000
>> 5,AB116UL,57.137546630000000,-2.112695886000000
>> ?.
>> Couple of million more lines
>>
>> The entire database schema looks like this. I know its complicated but
>> bear with me :)
>>
>> CREATE TABLE "postcode" (
>> "postcode" text NOT NULL,
>> "long" TEXT NOT NULL,
>> "lat" TEXT NOT NULL,
>> PRIMARY KEY("postcode")
>> );
>>
>> The only query that will ever run will be
>>
>> select long,lat from postcode where postcode = ?<some string>?
>>
>> Note I drop off the id field (column 0 in the CSV file) as its of no
>> interest to me. I also store the long and lat as strings as I don?t want
>> any number formatting changes at all. Rounding on a GPS number could cause
>> the wrong location to be used.
>>
>> The database will do nothing but return long and lat based on doing a
>> postcode lookup. There will never be any updates or changes. If there are,
>> the whole database will be regenerated.
>>
>> I need this to be as fast as possible and if necessary I?ll put it all in
>> RAM. The database is currently 120MB so it would easily fit in RAM. As it
>> never changes (perhaps 4 times per year), it could stay there.
>>
>> Is there anything else from the database schema side that would make
>> things quicker? e.g. If I created an index with postcode, long, lat in,
>> would that be quicker? or if i changed the long, lat to real (though I?m
>> reluctant to do so), would that make a lot of difference?
>>
>> Any suggestions gratefully received and apologies for not being able to
>> find it in the archives.
>>
>> Thanks,
>>
>> Rob
>> _______________________________________________
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
>
> --
>
> 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
