Re: [sqlite] How to speed up my queries?

[Stephen Oberholtzer]

On Wed, Jun 4, 2008 at 7:12 AM, Christophe Leske <[EMAIL PROTECTED]> wrote:

> Wilson, Ron P schrieb:
> > I'm not a guru yet, but I think you are not using the latlon index in
> > your query.  Perhaps if you index on lat and lon separately your query
> > will use those indices.  I think the lines below indicate using the
> > indices on class_dds and rowid.
> >
>
> Thanks also for the tiling hint, but my application is already written,
> and I have to stick to the databse given. I might reconsider though if
> the performance is still bad.
>

Does that mean you can't change the application at all?

Remember, the tiling (I call it "blockmap" since that's what Doom called it)
method doesn't require changing your existing table layout; it only requires
adding new tables.  Furthermore, the contents of these new tables can be
computed from the existing data in the Cities table -- all your application
would need to do is check for the existence of those tables, and if it
doesn't find them, create them and populate them.  It'd be an expensive
initial setup, but you only have to pay it once.

Also recall that SQLite supports multiple databases:

attach 'blockmap.db' as bm;
-- the following two tables are created in the blockmap.db file because they
have the 'bm.' qualifier
create table bm.blockmap (id integer primary key, lat real, long real);
create table bm.blockmapCity (blockmapId integer not null, cityId integer
not null);

-- but you don't need to use the 'bm.' qualifier, *unless* more than one of
the attached databases has a blockmapcity
-- by the way: tmp_blockfilter is a temporary table containing blockmap
IDs.  SQLite won't use an index for an IN clause, but it will use one if
you're joining against another table.
select c.* from Cities c join blockmapCity bc on bc.cityId=c.id join
tmp_blockfilter tb on tb.blockmapId = bc.blockmapId;

-- 
-- Stevie-O
Real programmers use COPY CON PROGRAM.EXE
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to speed up my queries?

[Griggs, Donald]

Hi Christophe,

Regarding:  What I find to be weird is that just ONE index seems to
yield the same results as several fields indexed:

Perhaps you're using this already, but prefixing your SELECT with
"EXPLAIN QUERY PLAN"  will quickly identify exactly which, if any
indicies are used.   It's a much briefer output than using just the
"EXPLAIN" prefix.

Also, Sqlite uses at most *one* index per table per SELECT, I believe.

 


This email and any attachments have been scanned for known viruses using 
multiple scanners. We believe that this email and any attachments are virus 
free, however the recipient must take full responsibility for virus checking. 
This email message is intended for the named recipient only. It may be 
privileged and/or confidential. If you are not the named recipient of this 
email please notify us immediately and do not copy it or use it for any 
purpose, nor disclose its contents to any other person.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to speed up my queries?

[Christophe Leske]

> class_dds has a maximum value of 6, so there where-clause "class_dds<11" 
> is totally unecessary - if i ditch this part, the response time is 
> coming down to 900ms from 2700ms for my request.
> I will now time again.
>   
Some new timings - i basically got it. What I find to be weird is that 
just ONE index seems to yield the same results as several fields indexed:

no indices:
-- "TIME for Between statement:2548"
-- 11
-- "TIME for <> statement:2528"
-- 11
-- "TIME for <> statement without ORDER BY-clause:1070"
-- 11
-- "TIME for <> statement without ORDER BY-clause:987"
-- 11

On index (longitude) - WHOAA!:

-- "TIME for Between statement:18"
-- 11
-- "TIME for <> statement:11"
-- 11
-- "TIME for <> statement without ORDER BY-clause:10"
-- 11
-- "TIME for <> statement without ORDER BY-clause:9"
-- 11

two indices (longitude and latitude):
-- "TIME for Between statement:11"
-- 11
-- "TIME for <> statement:11"
-- 11
-- "TIME for <> statement without ORDER BY-clause:10"
-- 11
-- "TIME for <> statement without ORDER BY-clause:10"
-- 11


3 field index (class_dds, longitude, latitude) - ?What gives?:
-- "TIME for Between statement:2540"
-- 11
-- "TIME for <> statement:2528"
-- 11
-- "TIME for <> statement without ORDER BY-clause:999"
-- 11
-- "TIME for <> statement without ORDER BY-clause:991"
-- 11

a 2 field index (longitude, latitude):
-- "TIME for Between statement:10"
-- 11
-- "TIME for <> statement:11"
-- 11
-- "TIME for <> statement without ORDER BY-clause:10"
-- 11
-- "TIME for <> statement without ORDER BY-clause:9"
-- 11


-- 
Christophe Leske

www.multimedial.de - [EMAIL PROTECTED]
http://www.linkedin.com/in/multimedial
Lessingstr. 5 - 40227 Duesseldorf - Germany
0211 261 32 12 - 0177 249 70 31


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to speed up my queries?

[Christophe Leske]

Christophe Leske schrieb:
>> Question, have you tried an index on class_dds, longitude_DDS, and 
>> latitude_DDS?
>>
>> CREATE INDEX tableidx ON table (class_dds, longitude_DDS, latitude_DDS);
>>
>> Since all three fields are used in the query, I am curious if that would 
>> help in any way.
>>   
>> 
> Doesn´t do anything, there is something else going here, i think - it 
> might well be that because of the order - statement, none of the indices 
> is actually being used:
>   
I got it:

SELECT * FROM Cities WHERE class_dds<11 and (longitude_DDS BETWEEN 
6.765103 and 7.089129) AND (latitude_DDS BETWEEN 44.261771 and 
44.424779) ORDER BY class_dds ASC Limit 20

class_dds has a maximum value of 6, so there where-clause "class_dds<11" 
is totally unecessary - if i ditch this part, the response time is 
coming down to 900ms from 2700ms for my request.

I will now time again.

-- 
Christophe Leske

www.multimedial.de - [EMAIL PROTECTED]
http://www.linkedin.com/in/multimedial
Lessingstr. 5 - 40227 Duesseldorf - Germany
0211 261 32 12 - 0177 249 70 31


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to speed up my queries?

[Christophe Leske]

> Question, have you tried an index on class_dds, longitude_DDS, and 
> latitude_DDS?
>
> CREATE INDEX tableidx ON table (class_dds, longitude_DDS, latitude_DDS);
>
> Since all three fields are used in the query, I am curious if that would 
> help in any way.
>   
Doesn´t do anything, there is something else going here, i think - it 
might well be that because of the order - statement, none of the indices 
is actually being used:

here some more timings (yours is at the end - the number between the 
timings is a count of the result sets just to make sure that they all 
return the right set of 11 results):

No indices whatsoever:
-- "TIME for Between statement:2794"
-- 11
-- "TIME for <> statement:2775"
-- 11

One index (longitude):
-- "TIME for Between statement:2776"
-- 11
-- "TIME for <> statement:2770"
-- 11

two indices (longitude and latitude separetely):
-- "TIME for Between statement:2786"
-- 11
-- "TIME for <> statement:2792"
-- 11

A 3 field index (class_dds, latitude_dds, longitude_dds):
-- "TIME for Between statement:2783"
-- 11
-- "TIME for <> statement:2762"
-- 11



-- 
Christophe Leske

www.multimedial.de - [EMAIL PROTECTED]
http://www.linkedin.com/in/multimedial
Lessingstr. 5 - 40227 Duesseldorf - Germany
0211 261 32 12 - 0177 249 70 31


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to speed up my queries?

[John Elrick]

Christophe Leske wrote:
> There is virtually no difference in using indices or not in my query.
>
> I also tried to reformulate my statement in order not to use BETWEEN but 
> a sandwiched > and < statement:
>
> SELECT * FROM Cities WHERE class_dds<11 and (longitude_DDS BETWEEN 
> 6.765103 and 7.089129) AND (latitude_DDS BETWEEN 44.261771 and 
> 44.424779) ORDER BY class_dds ASC Limit 20
>
> became
>
> SELECT * FROM Cities WHERE class_dds<11 and (longitude_DDS>6.765103 and 
> longitude_DDS<7.089129) AND (latitude_DDS>44.261771 and 
> latitude_DDS<44.424779) ORDER BY class_dds ASC Limit 20
>
>   
> The timing with a latlon index (latitude and longitude indexed):
> TIME:2814 ms
>
> The timing with just one index (latitude):
> TIME:2797 ms
>
> Timing with two indices (latitude and longitude separetly):
> TIME:2787 ms
>
>
> Timing with two indices (lat/lon) and the reformulated query above:
> TIME:2763 ms
>
>
>   

Question, have you tried an index on class_dds, longitude_DDS, and 
latitude_DDS?

CREATE INDEX tableidx ON table (class_dds, longitude_DDS, latitude_DDS);

Since all three fields are used in the query, I am curious if that would 
help in any way.


John
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to speed up my queries?

[Christophe Leske]

Federico Granata schrieb:
> can you post those rows with
> .mode insert
> so I can do a fast try ?
>   
INSERT INTO table VALUES('Pietraporzio',5,-1,7.032936,44.345913);
INSERT INTO table VALUES('Sambuco',5,-1,7.081367,44.33763);
INSERT INTO table VALUES('Le Pra',6,-1,6.88,44.316667);
INSERT INTO table VALUES('Bagni',6,-1,7.08,44.3);
INSERT INTO table VALUES('Argentera',6,-1,6.937569,44.396168);
INSERT INTO table VALUES('Bersezio',6,-1,6.970739,44.377898);
INSERT INTO table VALUES('Saint-Dalmas-le-Selvage',6,-1,6.867705,44.285194);
INSERT INTO table VALUES('Ferrere',6,-1,6.950052,44.354508);
INSERT INTO table VALUES('San Bernolfo',6,-1,7.039278,44.263371);
INSERT INTO table VALUES('Murenz',6,-1,6.998868,44.348969);
INSERT INTO table VALUES('Bagni di Vinadio',6,-1,7.074884,44.290033);

-- 
Christophe Leske

www.multimedial.de - [EMAIL PROTECTED]
http://www.linkedin.com/in/multimedial
Lessingstr. 5 - 40227 Duesseldorf - Germany
0211 261 32 12 - 0177 249 70 31


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to speed up my queries?

[Federico Granata]

can you post those rows with
.mode insert
so I can do a fast try ?
Tnx.

--
[image: Just A Little Bit Of
Geekness]
Le tre grandi virtù di un programmatore: pigrizia, impazienza e arroganza.
(Larry Wall).

2008/6/4 Christophe Leske <[EMAIL PROTECTED]>:

>
> > Can you give me some row of your db (also fake data are ok) so I try to
> > populate a db with 840k row and test your query on my machine ...
> >
> You can either take these rows here:
>
> Pietraporzio|5|-1|7.032936|44.345913
> Sambuco|5|-1|7.081367|44.33763
> Le Pra|6|-1|6.88|44.316667
> Bagni|6|-1|7.08|44.3
> Argentera|6|-1|6.937569|44.396168
> Bersezio|6|-1|6.970739|44.377898
> Saint-Dalmas-le-Selvage|6|-1|6.867705|44.285194
> Ferrere|6|-1|6.950052|44.354508
> San Bernolfo|6|-1|7.039278|44.263371
> Murenz|6|-1|6.998868|44.348969
> Bagni di Vinadio|6|-1|7.074884|44.290033
>
> or you can download a 170 MB big database similar to the one i am using,
> but populatet with free data from geonames.org from
>
> http://www.multimedial.de/earth/DB/free.7z
>
> That one is 1.4 Gb unpacked, and has yet no indices (i think, i prepared
> it some time ago).
>
> It should yield to similar results.
>
> Thanks for your time,
>
> --
> Christophe Leske
>
> www.multimedial.de - [EMAIL PROTECTED]
> http://www.linkedin.com/in/multimedial
> Lessingstr. 5 - 40227 Duesseldorf - Germany
> 0211 261 32 12 - 0177 249 70 31
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to speed up my queries?

[Christophe Leske]

There is virtually no difference in using indices or not in my query.

I also tried to reformulate my statement in order not to use BETWEEN but 
a sandwiched > and < statement:

SELECT * FROM Cities WHERE class_dds<11 and (longitude_DDS BETWEEN 
6.765103 and 7.089129) AND (latitude_DDS BETWEEN 44.261771 and 
44.424779) ORDER BY class_dds ASC Limit 20

became

SELECT * FROM Cities WHERE class_dds<11 and (longitude_DDS>6.765103 and 
longitude_DDS<7.089129) AND (latitude_DDS>44.261771 and 
latitude_DDS<44.424779) ORDER BY class_dds ASC Limit 20

The timing with a latlon index (latitude and longitude indexed):
TIME:2814 ms

The timing with just one index (latitude):
TIME:2797 ms

Timing with two indices (latitude and longitude separetly):
TIME:2787 ms


Timing with two indices (lat/lon) and the reformulated query above:
TIME:2763 ms


So all in all, there is no substantial speed gain to be found - it 
probably has to do with the fact that i am sorting at the end?

-- 
Christophe Leske

www.multimedial.de - [EMAIL PROTECTED]
http://www.linkedin.com/in/multimedial
Lessingstr. 5 - 40227 Duesseldorf - Germany
0211 261 32 12 - 0177 249 70 31


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to speed up my queries?

[Christophe Leske]

> Can you give me some row of your db (also fake data are ok) so I try to
> populate a db with 840k row and test your query on my machine ...
>   
You can either take these rows here:

Pietraporzio|5|-1|7.032936|44.345913
Sambuco|5|-1|7.081367|44.33763
Le Pra|6|-1|6.88|44.316667
Bagni|6|-1|7.08|44.3
Argentera|6|-1|6.937569|44.396168
Bersezio|6|-1|6.970739|44.377898
Saint-Dalmas-le-Selvage|6|-1|6.867705|44.285194
Ferrere|6|-1|6.950052|44.354508
San Bernolfo|6|-1|7.039278|44.263371
Murenz|6|-1|6.998868|44.348969
Bagni di Vinadio|6|-1|7.074884|44.290033

or you can download a 170 MB big database similar to the one i am using, 
but populatet with free data from geonames.org from

http://www.multimedial.de/earth/DB/free.7z

That one is 1.4 Gb unpacked, and has yet no indices (i think, i prepared 
it some time ago).

It should yield to similar results.

Thanks for your time,

-- 
Christophe Leske

www.multimedial.de - [EMAIL PROTECTED]
http://www.linkedin.com/in/multimedial
Lessingstr. 5 - 40227 Duesseldorf - Germany
0211 261 32 12 - 0177 249 70 31


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to speed up my queries?

[Federico Granata]

>
> Can someone tell me what kind of performance one is to expect from a
> 40Mb Sqlite database like the one I have?
>
if you put it on a floppy and throw  it out of the window it fall at 9.8 m/s
...

Can you give me some row of your db (also fake data are ok) so I try to
populate a db with 840k row and test your query on my machine ...
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to speed up my queries?

[Christophe Leske]

Wilson, Ron P schrieb:
> I'm not a guru yet, but I think you are not using the latlon index in
> your query.  Perhaps if you index on lat and lon separately your query
> will use those indices.  I think the lines below indicate using the
> indices on class_dds and rowid.
>   

Thanks to everyone who responded, i got some pretty good feedback.

Thanks also for the tiling hint, but my application is already written, 
and I have to stick to the databse given. I might reconsider though if 
the performance is still bad.

Can someone tell me what kind of performance one is to expect from a 
40Mb Sqlite database like the one I have?

-- 
Christophe Leske

www.multimedial.de - [EMAIL PROTECTED]
http://www.linkedin.com/in/multimedial
Lessingstr. 5 - 40227 Duesseldorf - Germany
0211 261 32 12 - 0177 249 70 31


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to speed up my queries?

[P Kishor]

On 6/3/08, D. Richard Hipp <[EMAIL PROTECTED]> wrote:
>
>  On Jun 3, 2008, at 10:27 AM, Christophe Leske wrote:
>  >
>  > We have a city database that is being queried regurlarly depending on
>  > the lat/long position of the viewport in order to show city names and
>  > labels.
>
>
>
> SQLite has an optional R-Tree engine.  The R-Tree is a new addition
>  and has not appeared in any release of SQLite, but it is available via
>  CVS.  There is little documentation other than a README file in the
>  source directory.  Nevertheless, an R-Tree is specifically designed to
>  solve the kind of query you describe above.  If you are willing to
>  work on the bleeding edge, you might want to investigate SQLite's R-
>  Tree capabilities.

Richard,

I am very interested in this. A few of us GIS-types have been
tinkering with this (http://sqlitegis.org), but working off what you
have been doing would likely be a lot better.


>
>
>
>  D. Richard Hipp
>  [EMAIL PROTECTED]
>
>
>
>
>  ___
>  sqlite-users mailing list
>  sqlite-users@sqlite.org
>  http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
Puneet Kishor http://punkish.eidesis.org/
Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/
Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to speed up my queries?

[D. Richard Hipp]

On Jun 3, 2008, at 10:27 AM, Christophe Leske wrote:
>
> We have a city database that is being queried regurlarly depending on
> the lat/long position of the viewport in order to show city names and
> labels.


SQLite has an optional R-Tree engine.  The R-Tree is a new addition  
and has not appeared in any release of SQLite, but it is available via  
CVS.  There is little documentation other than a README file in the  
source directory.  Nevertheless, an R-Tree is specifically designed to  
solve the kind of query you describe above.  If you are willing to  
work on the bleeding edge, you might want to investigate SQLite's R- 
Tree capabilities.


D. Richard Hipp
[EMAIL PROTECTED]



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to speed up my queries?

[Eric Minbiole]

> -- Even if you only go down to 1'-by-1' granularity, you've divided the
> world into 64,800 blocks.  Assuming that your 840K cities are all over the
> globe, and that about 70% of Earth is covered by water, that means that only
> about 20,000 blocks would actually have cities in them.  But with 840K
> cities, that means you're only considering about 42 cities for a single
> block.

Nice!  Though I haven't been part of this thread, I wanted to comment 
that this is a very elegant & efficient solution.  Kind of like a 2-D 
hash table.  I can see other areas where a similar approach would be 
helpful.  Thanks for a good tip-- I'm sure it will come in handy at some 
point.

~Eric

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to speed up my queries?

[Stephen Oberholtzer]

On Tue, Jun 3, 2008 at 1:27 PM, Christophe Leske <[EMAIL PROTECTED]> wrote:

> Hi,
>
> i am a new member of this list and interested in speeding up my sqlite
> queries.
>
> I am using SQlite in a 3d environment which is close to Google Earth or
> Nasa WorldWind.
>
> We have a city database that is being queried regurlarly depending on
> the lat/long position of the viewport in order to show city names and
> labels.




>From what I gather,  you've got something very similar to the 2D-spatial
problem:

"I need to find cities that are within R miles of (X,Y)."

This translates as

"I need to find objects that are within the circle with origin (X, Y) and
radius=R".

This is fundamentally a collision-detection algorithm, and I have a
suggestion that might help, based on the way old DOS game Doom optimized its
collision detection code.

Here's the setup: You divide the world into equally-sized blocks of width W
and height H. Let's say, for the sake of argument, that W=1' and H=1' (this
is huge, but it helps illustrate the point)
Since the world is 180 degrees north-to-south and 360 degrees around the
equator, this gives 64,800 blocks.  So, for example:

CREATE TABLE blockmap (id integer not null primary key, lat real, long
real);

Then you need to build a correspondence table:

CREATE TABLE blockmapCity(blockmapId, cityId);

A naive implementation might only mark a city's center, while a more
advanced version might get fancy and have an approximate size of the city
and place it in multiple blockmaps, in case it was big enough to spill over
into adjacent blocks.

What you do then, in order to do a lookup, is to find all of the blocks that
intersect with your circle.  This can be done easily with the right math.
Then, once you've figured out which blocks to include, you just filter out
the relevant cities from blockmapCity.  Once you have *those* cities you can
filter them out as precisely as you were doing before.

Some notes:

-- Even if you only go down to 1'-by-1' granularity, you've divided the
world into 64,800 blocks.  Assuming that your 840K cities are all over the
globe, and that about 70% of Earth is covered by water, that means that only
about 20,000 blocks would actually have cities in them.  But with 840K
cities, that means you're only considering about 42 cities for a single
block.
-- The algorithm used to prune down the set of blocks to include doesn't
need to be perfect. Remember, this is all an optimization; even if you
return every blockmap in the same *hemisphere*, you'd still be searching
through only 420K cities instead of 840K!


If you need any more help implementing something like this, go ahead and
reply to the list.
If you can provide a concrete set of data (for example, all or most or at
least a significant number of the cities in the US) I can help put together
a more concrete example.



>
> Plus, there are additional databases for special features, like natural
> hazards and catastrophies.
>
> The city database has around 840.000 records,  the following schema and
> weights currently short under 40Mb:
>
> sqlite> .schema cities
> CREATE TABLE Cities (NAME_DDS TEXT, CLASS_DDS NUMERIC, POPEST_DDS
> NUMERIC, LONGI
> TUDE_DDS NUMERIC, LATITUDE_DDS NUMERIC);
> CREATE INDEX class ON Cities(CLASS_DDS ASC);
> CREATE INDEX latlon on Cities(latitude_dds,longitude_dds);
>
> My questions are:
>
> - how do I speed up the queries? For small lat/long windows, and high
> classes for the cities, i get long query times (e.g. about 600ms)
> Is this reasonable to ask for, or IS that already a top speed for this
> kind of query?
>
> - I have indexed latitude AND longitude,as you can see above. Is this ok?
>
> - I came across the EXLPAIN command, and have read an email by someone
> on this list on how to analyze my queries. I should probably do that,
> yet i am unfamiliar with reading the output of the Explain command.
>
> Thanks for your time and eventual help,
>
> --
> Christophe Leske
>
> www.multimedial.de - [EMAIL PROTECTED]
> http://www.linkedin.com/in/multimedial
> Lessingstr. 5 - 40227 Duesseldorf - Germany
> 0211 261 32 12 - 0177 249 70 31
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
-- Stevie-O
Real programmers use COPY CON PROGRAM.EXE
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to speed up my queries?

[Jay A. Kreibich]

On Tue, Jun 03, 2008 at 07:56:11PM +0200, Christophe Leske scratched on the 
wall:

> A typical query that causes problems would be:
> 
> SELECT * FROM Cities WHERE class_dds<11 and (longitude_DDS BETWEEN 
> 6.765103 and 7.089129) AND (latitude_DDS BETWEEN 44.261771 and 
> 44.424779) ORDER BY class_dds ASC Limit 20
> 
> Am i right that no matter what limit is given to the SQL statement, the 
> complete query is executed first, AND THEN filtered according to the 
> limit? This is what i think i a seeing here...

  The limit is applied *after* the ORDER so the system has no choice
  but to find every match for the WHERE statement, then ORDER it, then
  LIMIT it.

  In theory, the system could walk the index on class_dds to get the
  ORDER BY "for free" (and could then terminate the query as soon as
  the LIMIT is reached), but I can guess the nature of class_dss will
  prevent this.  Basically if any one value is contained in 5 to 10% of
  the rows, an index won't be used and the system will do a full
  table-scan (this isn't unique to SQLite; nearly all DBs do this
  because it is faster in the general case).

  It also seems unlikely that the index will be of much use unless
  you're looking for specific values.  An index can be used for a
  range, but not a double-range like you've got going here.  This is
  part of the reason why many databases offer GIS extensions... the
  indexing problem for space is non-trivial.

  I'd try dropping the latlog index and just making one on lat.  Put
  the lat and long conditions first, as they're going to give you the
  most filtering for the least cost.  Try using dual GT/LT clauses
  rather than BETWEEN if the lat index still isn't used.

> I am therefore also after something that cuts off the query after a 
> certain amount of results have been found.

  That isn't going to happen unless you can get rid of the ORDER *or*
  make the ORDER on something that is used as an index.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"'People who live in bamboo houses should not throw pandas.' Jesus said that."
   - "The Ninja", www.AskANinja.com, "Special Delivery 10: Pop!Tech 2006"
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to speed up my queries?

[Petite Abeille]

On Jun 3, 2008, at 7:27 PM, Christophe Leske wrote:

> i am a new member of this list and interested in speeding up my  
> sqlite queries.

There are no magic bullets, but "The SQLite Query Optimizer Overview"  
is a good read:

http://www.sqlite.org/optoverview.html

As well as "Query Plans":

http://www.sqlite.org/cvstrac/wiki?p=QueryPlans

Once the basics are covered and you still need to speed up things,  
take a look at physically partitioning your data in several database  
files. This might reduce quite noticeably the overall processing time  
for a given query at the cost of a bit of added code complexity.

--
PA.
http://alt.textdrive.com/nanoki/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to speed up my queries?

[Wilson, Ron P]

PS. Also, I am not sure about the BETWEEN command - does it use indices?
If not you could write the query without BETWEEN.

RW

Ron Wilson, S/W Systems Engineer III, Tyco Electronics, 434.455.6453

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Christophe Leske
Sent: Tuesday, June 03, 2008 1:56 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] How to speed up my queries?

Wilson, Ron P schrieb:
> Hi Christophe,
>
> 1. Please give us an example query.  SELECT * FROM Cities where
> LONGITUDE_DDS=? AND LATITUDE_DDS=?
> 2. Paste in the EXPLAIN results from the command line tool.
> 3. Is the database file local or are you accessing it over a network?
>   
Hi,

the database file is local, right next to the app using it. I am using 
the sqlite3.exe command line tool for the queries, but would eventually 
like to ditch it for the native support.
However, since the app I am using is a single threaded application 
(Adobe Director), eventual queries that take too long to complete do 
completely block the app which is why i have threaded the queries using 
a multi-threaded shell extension which does the queries, then reports 
back the results.

A typical query that causes problems would be:

SELECT * FROM Cities WHERE class_dds<11 and (longitude_DDS BETWEEN 
6.765103 and 7.089129) AND (latitude_DDS BETWEEN 44.261771 and 
44.424779) ORDER BY class_dds ASC Limit 20

Am i right that no matter what limit is given to the SQL statement, the 
complete query is executed first, AND THEN filtered according to the 
limit? This is what i think i a seeing here...

I am therefore also after something that cuts off the query after a 
certain amount of results have been found.

The explain results from the command line tool:

sqlite> EXPLAIN SELECT * FROM Cities WHERE class_dds<11 and 
(longitude_DDS BETWE
EN 6.765103 and 7.089129) AND (latitude_DDS BETWEEN 44.261771 and 
44.424779) ORD
ER BY class_dds ASC Limit 20
   ...> ;
addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
0 Trace  0 0 0 EXPLAIN SELECT * FROM Cities 
WHERE class_
dds<11 and (longitude_DDS BETWEEN 6.765103 and 7.089129) AND 
(latitude_DDS BETWE
EN 44.261771 and 44.424779) ORDER BY class_dds ASC Limit 20
;  00
1 Noop   0 0 000
2 Integer201 000
3 MustBeInt  1 0 000
4 IfZero 1 42000
5 Integer112 000
6 Real   0 3 0 6.765103   00
7 Real   0 4 0 7.089129   00
8 Real   0 5 0 44.2617710001  00
9 Real   0 6 0 44.424779  00
10Goto   0 43000
11SetNumColumns  0 6 000
12OpenRead   0 3 000
13SetNumColumns  0 2 000
14OpenRead   2 6 0 keyinfo(1,BINARY)  00
15Rewind 2 408 0  00
16SCopy  2 8 000
17IsNull 8 40000
18Affinity   8 1 0 cb 00
19IdxGE  2 408 1  00
20Column 2 0 11   00
21IsNull 1139000
22IdxRowid   2 11000
23MoveGe 0 0 11   00
24Column 0 3 12   00
25Lt 3 3912collseq(BINARY)  6b
26Gt 4 3912collseq(BINARY)  6b
27Column 0 4 17   00
28Lt 5 3917collseq(BINARY)  6b
29Gt 6 3917collseq(BINARY)  6b
30Column 0 0 22   00
31Column 2 0 23   00
32Column 0 2 24   00
33Column 0 3 25   00
34Column 0 4 26   00
35Column 0 5 27   00
36ResultRow  226 000
37AddImm 1 -1000
38IfZero 1 40000
39Next   2 19000
40Close  0 0 000
41Close  2 0 000
42Halt   0 0 000
43Transaction0 0 000
44VerifyCookie   0 202   000
45TableLock  0 3 0 Cities 00
46Goto  

Re: [sqlite] How to speed up my queries?

[Wilson, Ron P]

I'm not a guru yet, but I think you are not using the latlon index in
your query.  Perhaps if you index on lat and lon separately your query
will use those indices.  I think the lines below indicate using the
indices on class_dds and rowid.

19IdxGE  2 408 1  00
22IdxRowid   2 11000

LIMIT 20 should also limit the query to the first 20 matches; i.e. I
don't think it is actually finding N results and filtering down to the
first 20.  At least I think that's what this means:

37AddImm 1 -1000
38IfZero 1 40000

RW

Ron Wilson, S/W Systems Engineer III, Tyco Electronics, 434.455.6453

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Christophe Leske
Sent: Tuesday, June 03, 2008 1:56 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] How to speed up my queries?

Wilson, Ron P schrieb:
> Hi Christophe,
>
> 1. Please give us an example query.  SELECT * FROM Cities where
> LONGITUDE_DDS=? AND LATITUDE_DDS=?
> 2. Paste in the EXPLAIN results from the command line tool.
> 3. Is the database file local or are you accessing it over a network?
>   
Hi,

the database file is local, right next to the app using it. I am using 
the sqlite3.exe command line tool for the queries, but would eventually 
like to ditch it for the native support.
However, since the app I am using is a single threaded application 
(Adobe Director), eventual queries that take too long to complete do 
completely block the app which is why i have threaded the queries using 
a multi-threaded shell extension which does the queries, then reports 
back the results.

A typical query that causes problems would be:

SELECT * FROM Cities WHERE class_dds<11 and (longitude_DDS BETWEEN 
6.765103 and 7.089129) AND (latitude_DDS BETWEEN 44.261771 and 
44.424779) ORDER BY class_dds ASC Limit 20

Am i right that no matter what limit is given to the SQL statement, the 
complete query is executed first, AND THEN filtered according to the 
limit? This is what i think i a seeing here...

I am therefore also after something that cuts off the query after a 
certain amount of results have been found.

The explain results from the command line tool:

sqlite> EXPLAIN SELECT * FROM Cities WHERE class_dds<11 and 
(longitude_DDS BETWE
EN 6.765103 and 7.089129) AND (latitude_DDS BETWEEN 44.261771 and 
44.424779) ORD
ER BY class_dds ASC Limit 20
   ...> ;
addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
0 Trace  0 0 0 EXPLAIN SELECT * FROM Cities 
WHERE class_
dds<11 and (longitude_DDS BETWEEN 6.765103 and 7.089129) AND 
(latitude_DDS BETWE
EN 44.261771 and 44.424779) ORDER BY class_dds ASC Limit 20
;  00
1 Noop   0 0 000
2 Integer201 000
3 MustBeInt  1 0 000
4 IfZero 1 42000
5 Integer112 000
6 Real   0 3 0 6.765103   00
7 Real   0 4 0 7.089129   00
8 Real   0 5 0 44.2617710001  00
9 Real   0 6 0 44.424779  00
10Goto   0 43000
11SetNumColumns  0 6 000
12OpenRead   0 3 000
13SetNumColumns  0 2 000
14OpenRead   2 6 0 keyinfo(1,BINARY)  00
15Rewind 2 408 0  00
16SCopy  2 8 000
17IsNull 8 40000
18Affinity   8 1 0 cb 00
19IdxGE  2 408 1  00
20Column 2 0 11   00
21IsNull 1139000
22IdxRowid   2 11000
23MoveGe 0 0 11   00
24Column 0 3 12   00
25Lt 3 3912collseq(BINARY)  6b
26Gt 4 3912collseq(BINARY)  6b
27Column 0 4 17   00
28Lt 5 3917collseq(BINARY)  6b
29Gt 6 3917collseq(BINARY)  6b
30Column 0 0 22   00
31Column 2 0 23   00
32Column 0 2 24   00
33Column 0 3 25   00
34Column 0 4 26   00
35Column 0 5 27   00
36ResultRow  226 0

Re: [sqlite] How to speed up my queries?

[Christophe Leske]

Wilson, Ron P schrieb:
> Hi Christophe,
>
> 1. Please give us an example query.  SELECT * FROM Cities where
> LONGITUDE_DDS=? AND LATITUDE_DDS=?
> 2. Paste in the EXPLAIN results from the command line tool.
> 3. Is the database file local or are you accessing it over a network?
>   
Hi,

the database file is local, right next to the app using it. I am using 
the sqlite3.exe command line tool for the queries, but would eventually 
like to ditch it for the native support.
However, since the app I am using is a single threaded application 
(Adobe Director), eventual queries that take too long to complete do 
completely block the app which is why i have threaded the queries using 
a multi-threaded shell extension which does the queries, then reports 
back the results.

A typical query that causes problems would be:

SELECT * FROM Cities WHERE class_dds<11 and (longitude_DDS BETWEEN 
6.765103 and 7.089129) AND (latitude_DDS BETWEEN 44.261771 and 
44.424779) ORDER BY class_dds ASC Limit 20

Am i right that no matter what limit is given to the SQL statement, the 
complete query is executed first, AND THEN filtered according to the 
limit? This is what i think i a seeing here...

I am therefore also after something that cuts off the query after a 
certain amount of results have been found.

The explain results from the command line tool:

sqlite> EXPLAIN SELECT * FROM Cities WHERE class_dds<11 and 
(longitude_DDS BETWE
EN 6.765103 and 7.089129) AND (latitude_DDS BETWEEN 44.261771 and 
44.424779) ORD
ER BY class_dds ASC Limit 20
   ...> ;
addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
0 Trace  0 0 0 EXPLAIN SELECT * FROM Cities 
WHERE class_
dds<11 and (longitude_DDS BETWEEN 6.765103 and 7.089129) AND 
(latitude_DDS BETWE
EN 44.261771 and 44.424779) ORDER BY class_dds ASC Limit 20
;  00
1 Noop   0 0 000
2 Integer201 000
3 MustBeInt  1 0 000
4 IfZero 1 42000
5 Integer112 000
6 Real   0 3 0 6.765103   00
7 Real   0 4 0 7.089129   00
8 Real   0 5 0 44.2617710001  00
9 Real   0 6 0 44.424779  00
10Goto   0 43000
11SetNumColumns  0 6 000
12OpenRead   0 3 000
13SetNumColumns  0 2 000
14OpenRead   2 6 0 keyinfo(1,BINARY)  00
15Rewind 2 408 0  00
16SCopy  2 8 000
17IsNull 8 40000
18Affinity   8 1 0 cb 00
19IdxGE  2 408 1  00
20Column 2 0 11   00
21IsNull 1139000
22IdxRowid   2 11000
23MoveGe 0 0 11   00
24Column 0 3 12   00
25Lt 3 3912collseq(BINARY)  6b
26Gt 4 3912collseq(BINARY)  6b
27Column 0 4 17   00
28Lt 5 3917collseq(BINARY)  6b
29Gt 6 3917collseq(BINARY)  6b
30Column 0 0 22   00
31Column 2 0 23   00
32Column 0 2 24   00
33Column 0 3 25   00
34Column 0 4 26   00
35Column 0 5 27   00
36ResultRow  226 000
37AddImm 1 -1000
38IfZero 1 40000
39Next   2 19000
40Close  0 0 000
41Close  2 0 000
42Halt   0 0 000
43Transaction0 0 000
44VerifyCookie   0 202   000
45TableLock  0 3 0 Cities 00
46Goto   0 11000

-- 
Christophe Leske

www.multimedial.de - [EMAIL PROTECTED]
http://www.linkedin.com/in/multimedial
Lessingstr. 5 - 40227 Duesseldorf - Germany
0211 261 32 12 - 0177 249 70 31


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to speed up my queries?

[Wilson, Ron P]

Hi Christophe,

1. Please give us an example query.  SELECT * FROM Cities where
LONGITUDE_DDS=? AND LATITUDE_DDS=?
2. Paste in the EXPLAIN results from the command line tool.
3. Is the database file local or are you accessing it over a network?

RW

Ron Wilson, S/W Systems Engineer III, Tyco Electronics, 434.455.6453
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Christophe Leske
Sent: Tuesday, June 03, 2008 1:27 PM
To: General Discussion of SQLite Database
Subject: [sqlite] How to speed up my queries?

Hi,
 
i am a new member of this list and interested in speeding up my sqlite 
queries.

I am using SQlite in a 3d environment which is close to Google Earth or 
Nasa WorldWind.

We have a city database that is being queried regurlarly depending on 
the lat/long position of the viewport in order to show city names and 
labels.

Plus, there are additional databases for special features, like natural 
hazards and catastrophies.

The city database has around 840.000 records,  the following schema and 
weights currently short under 40Mb:

sqlite> .schema cities
CREATE TABLE Cities (NAME_DDS TEXT, CLASS_DDS NUMERIC, POPEST_DDS 
NUMERIC, LONGI
TUDE_DDS NUMERIC, LATITUDE_DDS NUMERIC);
CREATE INDEX class ON Cities(CLASS_DDS ASC);
CREATE INDEX latlon on Cities(latitude_dds,longitude_dds);

My questions are:

- how do I speed up the queries? For small lat/long windows, and high 
classes for the cities, i get long query times (e.g. about 600ms)
Is this reasonable to ask for, or IS that already a top speed for this 
kind of query?

- I have indexed latitude AND longitude,as you can see above. Is this
ok?

- I came across the EXLPAIN command, and have read an email by someone 
on this list on how to analyze my queries. I should probably do that, 
yet i am unfamiliar with reading the output of the Explain command.

Thanks for your time and eventual help,

-- 
Christophe Leske

www.multimedial.de - [EMAIL PROTECTED]
http://www.linkedin.com/in/multimedial
Lessingstr. 5 - 40227 Duesseldorf - Germany
0211 261 32 12 - 0177 249 70 31


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users