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

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

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

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

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

> 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

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

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. >>

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

> 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

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

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

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

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

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

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

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

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

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

> 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

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

> > 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

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

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

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

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

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

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

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

> -- 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

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

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

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

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

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

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":

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

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=

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

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

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

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

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