From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
behalf of Jonathan Haws [jonathan.h...@sdl.usu.edu]
Sent: Friday, October 29, 2010 11:50 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Slow SELECT Statements in Large Database file
I agree
[sqlite-users-boun...@sqlite.org] on
behalf of Roger Binns [rog...@rogerbinns.com]
Sent: Friday, October 29, 2010 12:09 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Slow SELECT Statements in Large Database file
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On 10/29/2010 10:48
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On 10/29/2010 10:48 AM, Jonathan Haws wrote:
> We have a whole ton of points (3600^2) and a single select returns a single
> point - though I may modify the select to return the four corners of the box
> corresponding to the point that was entered.
-boun...@sqlite.org] on
behalf of Jim Wilcoxson [pri...@gmail.com]
Sent: Friday, October 29, 2010 10:29 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Slow SELECT Statements in Large Database file
Jonathan - 500 queries per second is 2ms per query. You'll have a hard time
ge
] Slow SELECT Statements in Large Database file
You should see a noticeable increase in speed with the index you show.
You'll want to make sure your data has been inserted in dted_lat order or
dted_lon order.
That way all the matching records will be colocated on disk and in memory.
Othe
...@sqlite.org [sqlite-users-boun...@sqlite.org] on
behalf of Simon Slavin [slav...@bigfraud.org]
Sent: Friday, October 29, 2010 10:14 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Slow SELECT Statements in Large Database file
On 29 Oct 2010, at 5:07pm, Jonathan Haws wrote
On Fri, Oct 29, 2010 at 12:07 PM, Jonathan Haws
wrote:
> All,
>
> I am having some problems with a new database that I am trying to setup.
>
> This database is a large file (about 8.7 GB without indexing). The problem
> I am having is that SELECT statements are extremely slow. The goal is to
> g
Jonathan - 500 queries per second is 2ms per query. You'll have a hard time
getting that kind of speed for random queries with any rotating media. Your
database needs to be in memory - all of it, not just indexes - or on a flash
drive.
If your queries are not random but are somehow related, eg,
You should see a noticeable increase in speed with the index you show.
You'll want to make sure your data has been inserted in dted_lat order or
dted_lon order.
That way all the matching records will be colocated on disk and in memory.
Otherwise it's going to do a lot of paging to get your re
On 29 Oct 2010, at 5:07pm, Jonathan Haws wrote:
> SELECT dted_alt FROM Dted WHERE (dted_lat=%d AND dted_lon=%d);
>
> The numbers fed to dted_lat and dted_lon are typically on the order of
> 37 and -111.
>
> What can we do to speed up our SELECT statements? Minutes is
> unacceptable for
10 matches
Mail list logo
