A quick update on handling the the select query. We have some speed results
which are quite illuminating and positive.
We?ve been playing around with building a few web services, one of which was
the postcode lookup that people have kindly helped with.
We have one service that is a simple
On 20 Sep 2015, at 11:18pm, James K. Lowden wrote:
> Rob Willett wrote:
>
>> Thanks. We?ve got 100 requests a second which may be enough. We?ll
>> keep looking though for any time.
>
> I suppose you know that 100/sec is about 0.1% of what the machine is
> capable of.
SQLite is really a
On Sat, 19 Sep 2015 14:24:24 +0100
Rob Willett wrote:
> Thanks. We?ve got 100 requests a second which may be enough. We?ll
> keep looking though for any time.
I suppose you know that 100/sec is about 0.1% of what the machine is
capable of.
You spoke of read-only data that changes
Em 19/09/2015 11:13, "Rob Willett" <
mail.robertwillett.com at postfix.robertwillett.com> escreveu:
>
> Ryan,
>
> Thanks. We?ve got 100 requests a second which may be enough. We?ll keep
looking though for any time.
>
Have you considered to implement the service using a compiled language,
Ryan,
Thanks. We?ve got 100 requests a second which may be enough. We?ll keep looking
though for any time.
Rob.
> On 18 Sep 2015, at 18:26, R.Smith wrote:
>
> >>>Rob: "We want to do postal code ==> GPS..."
>
> >>Me: "You can use google apis..."
>
> >Rob: "Our business is GPS and GIS
>>>Rob: "We want to do postal code ==> GPS..."
>>Me: "You can use google apis..."
>Rob: "Our business is GPS and GIS traffic data"
Oops yes, that's a whole nother kettle of fish then. To return to some
of the older parts of the conversation, I think the SQL route is best
(whichever
On 2015-09-18 06:23 PM, Luuk wrote:
>
>
> On 18-09-15 17:54, R.Smith wrote:
>>
>> As an aside, you know you can already do this via a Google API right?
>> That is, you can find a GPS location for an address or approximate
>> address (city & postal code will do). Usually, if the address is not
On 18-09-15 17:54, R.Smith wrote:
>
> As an aside, you know you can already do this via a Google API right?
> That is, you can find a GPS location for an address or approximate
> address (city & postal code will do). Usually, if the address is not
> specific, the nearest road intersection GPS
On 2015-09-18 08:17 AM, Rob Willett wrote:
> What we wanted was a postcode to GPS lookup, it was not to pinpoint a house,
> you would need a little more information to be that specific, e.,g. house
> number or a name, but to get to a general area that has a central GPS point.
As an aside, you
Yes we are aware of the Google API. Our business is GPS and GIS traffic data so
we have actually built a lot of the infrastructure ourselves. We have our own
map server running OpenStreetMap, a routing server and database allowing us to
do the same queries as you mention. Its actually a lot
> On Thu, 17 Sep 2015 13:55:51 +0100 Simon Slavin wrote
>
>> At the end of the CREATE TABLE statement add keywords: "WITHOUT
>> ROWID". Like this:
>>
>> CREATE TABLE postcode(
>> postcode TEXT,
>> long TEXT,
>> lat TEXT
>> ) WITHOUT ROWID;
>
> The documentation suggests that you need to
Chris,
The postcode is unique in this data collection. I *should* have stated that as
it is important. My apologies.
The UK postcode is defined to cover several houses, in London my postcode
covers around 20 houses, all of them on my street. This means the area it
covers is pretty small.
On 18 Sep 2015, at 1:44am, Chris Waters wrote:
> In this particular thread I think an important element is that postcode will
> not be unique
Arghh ! If postcodes are not unique in that table then the answer needs to
be revised.
Simon.
Nicolas,
Speed of development, ease of development, maintenance, available frameworks,
available Perl modules.
I actually know C the best of any language and will very happily code in C, but
for some things its quicker to develop it in out in Perl. Certainly for
anything involving
On 2015-09-17 04:50 PM, John McKown wrote:
> On Thu, Sep 17, 2015 at 9:41 AM, R.Smith wrote:
>
>> (11) This forum. Need help or suggestions with how to optimize your
>> internal value-store? Not so easy. Need help optimizing an SQLite DB or
>> query? We've all seen the very clever people here
On 2015-09-17 02:22 PM, Richard Hipp wrote:
> On 9/17/15, John McKown wrote:
>> Well, this may be a heretical answer.
> Yes, it is.
>
> There are a number of reasons to still use SQLite even if you are only
> doing key/value lookups:
>
> (1) SQLite is the most widely deployed database engine in
John,
The problem was the version of SQLite included in DBD. Its way old?.
I am a sysadmin as well as developer as well as tester as well as CFO. I wear
many hats in this startup :)
Our conclusion is that we need to look at the architecture again to see if the
web services can be speeded up.
John,
The problem was the version of SQLite included in DBD. Its way old?.
I am a sysadmin as well as developer as well as tester as well as CFO. I wear
many hats in this startup :)
Our conclusion is that we need to look at the architecture again to see if the
web services can be speeded up.
Simon,
We understand how SQlite is compiled into programs. We weren?t sure if the
issues were Navicat which we sometimes use for a front end or the Perl DB
stuff. My money was on Navicat being the problem. I was wrong, it was the DB
version.
The sqlite version is 3.7.17 on Ubuntu 14.04
> On 17 Sep 2015, at 2:47pm, Rob Willett
> wrote:
>
> 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
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.
Simon,
Fair point and agreed. We?ll update it.
Rob
> On 17 Sep 2015, at 13:56, Simon Slavin wrote:
>
>
> On 17 Sep 2015, at 12:58pm, Rob Willett
> wrote:
>
>> CREATE TABLE "postcode" (
>> "postcode" text NOT NULL,
>
> As an incidental tip not related to your question, I would
On 17 Sep 2015, at 12:58pm, Rob Willett wrote:
> CREATE TABLE "postcode" (
>"postcode" text NOT NULL,
As an incidental tip not related to your question, I would recommend that you
don't have a column name which is the same as a table name. Call the table
'locations' instead or
On 17 Sep 2015, at 1:13pm, Richard Hipp wrote:
> At the end of the CREATE TABLE statement add keywords: "WITHOUT
> ROWID". Like this:
>
> CREATE TABLE postcode(
> postcode TEXT,
> long TEXT,
> lat TEXT
> ) WITHOUT ROWID;
The documentation suggests that you need to explicitly declare a
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,
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
hi Rob (Willet),
May I ask why are you using Perl instead of C/C++ ? I'm not going to start a
debate, I'm just
curious.
regards,
Nicolas
On Thu, Sep 17, 2015 at 9:41 AM, R.Smith wrote:
>
>
> On 2015-09-17 02:22 PM, Richard Hipp wrote:
>
>> On 9/17/15, John McKown wrote:
>>
>>> Well, this may be a heretical answer.
>>>
>> Yes, it is.
>>
>> ??
??
> (11) This forum. Need help or suggestions with how to optimize your
> internal
The latest PERL DBI for SQLite that I could see is at
http://www.cpan.org/authors/id/I/IS/ISHIGAKI/DBD-SQLite-1.48.tar.gz . I
took a look at it and it has sqlite3.c (and others) from the 3.8.10
amalgamation in it.
Just "for fun", I copied 3 files (sqlite3.c, sqlite3.h, and sqlite3ext.h)
from my
On 9/17/15, John McKown wrote:
> Well, this may be a heretical answer.
Yes, it is.
There are a number of reasons to still use SQLite even if you are only
doing key/value lookups:
(1) SQLite is the most widely deployed database engine in the world,
so it is more likely to already be on the
On Thu, Sep 17, 2015 at 7:21 AM, Rob Willett
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
On 9/17/15, Rob Willett wrote:
>
> 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
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
33 matches
Mail list logo
