Re: [sqlite] Slow SELECT Statements in Large Database file

[Jonathan Haws]

The indexing took my search time from over three minutes down to less that 2 
milliseconds.  I am happy with those times.

Thanks to everyone for the help!

Jonathan



--
Jonathan R. Haws
Electrical Engineer
Space Dynamics Laboratory
(435) 797-4629
jh...@sdl.usu.edu



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, and for that reason we are keeping the DB on an SSD.  Initial 
benchmarks show that we should be able to get the performance we need - I am 
just not getting it with my new database.

I am sure I have something setup wrong, but maybe it is just the fact that the 
indexing has not taken place yet.  Once that has finished I will report back 
and see what kind of performance I get.

Thanks,

--
Jonathan R. Haws
Electrical Engineer
Space Dynamics Laboratory
(435) 797-4629
jh...@sdl.usu.edu



From: sqlite-users-boun...@sqlite.org [sqlite-users-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
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 are doing
thousands of queries within a small area, and the db records were also
inserted by area, you may have better luck.

Jim
--
HashBackup: easy onsite and offsite Unix backup
http://sites.google.com/site/hashbackup


On Fri, Oct 29, 2010 at 12:07 PM, Jonathan Haws
<jonathan.h...@sdl.usu.edu>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
> get the database file up and running for an embedded application (we have
> tons of storage space so the size is not a problem).
>
> Here is the schema layout:
>
> CREATE table Dted (dtedkey INTEGER PRIMARY KEY, dted_lat
> INTEGER, dted_lon INTEGER, dted_alt FLOAT);
>
> We lookup dted_alt based on dted_lat and dted_lon.  Here is our SELECT
> statement:
>
> 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 our application.  We were hoping we could run somewhere
> on the order of 500 queries per second and get valid results back.
>
> I am not an SQL expert, but I was reading about indexes that that it is
> best to have a specific index per SELECT.  Since we only have one,
> this is the index I am creating now (it has been creating this index on my
> machine for the past 10 minutes now):
>
> CREATE INDEX idx_ll ON Dted(dted_lat,dted_lon);
>
> Is that a good index for my SELECT?  Will it speed up the accesses?
>
> Any thoughts?
>
>
> Thanks!
> --
> Jonathan
> ___
> 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
___
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

[sqlite] Slow SELECT Statements in Large Database file

[Jonathan Haws]

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
get the database file up and running for an embedded application (we have 
tons of storage space so the size is not a problem).

Here is the schema layout:

CREATE table Dted (dtedkey INTEGER PRIMARY KEY, dted_lat
INTEGER, dted_lon INTEGER, dted_alt FLOAT);

We lookup dted_alt based on dted_lat and dted_lon.  Here is our SELECT
statement:

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 our application.  We were hoping we could run somewhere
on the order of 500 queries per second and get valid results back.

I am not an SQL expert, but I was reading about indexes that that it is
best to have a specific index per SELECT.  Since we only have one,
this is the index I am creating now (it has been creating this index on my
machine for the past 10 minutes now):

CREATE INDEX idx_ll ON Dted(dted_lat,dted_lon);

Is that a good index for my SELECT?  Will it speed up the accesses?

Any thoughts?


Thanks!
--
Jonathan

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

Re: [sqlite] Slow SELECT Statements in Large Database file

[Jonathan Haws]

I was actually just reading through that.  I may switch over to that kind of 
implementation.

Thanks for the tip.

--
Jonathan R. Haws
Electrical Engineer
Space Dynamics Laboratory
(435) 797-4629
jh...@sdl.usu.edu



From: sqlite-users-boun...@sqlite.org [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 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.

Are you aware that SQLite has an RTree extension (written by one of the
SQLite developers) that seems to substantially overlap with what you are doing?

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

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAkzLDbwACgkQmOOfHg372QSayQCg2+cGQyf88up8V2MsqV6qIdq2
Vi0AoIpHo9HICTMVuYImqW2dr1E47Ddu
=eesF
-END PGP SIGNATURE-
___
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] Slow SELECT Statements in Large Database file

[Roger Binns]

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

Are you aware that SQLite has an RTree extension (written by one of the
SQLite developers) that seems to substantially overlap with what you are doing?

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

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAkzLDbwACgkQmOOfHg372QSayQCg2+cGQyf88up8V2MsqV6qIdq2
Vi0AoIpHo9HICTMVuYImqW2dr1E47Ddu
=eesF
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Slow SELECT Statements in Large Database file

[Jonathan Haws]

I agree, and for that reason we are keeping the DB on an SSD.  Initial 
benchmarks show that we should be able to get the performance we need - I am 
just not getting it with my new database.

I am sure I have something setup wrong, but maybe it is just the fact that the 
indexing has not taken place yet.  Once that has finished I will report back 
and see what kind of performance I get.

Thanks,

--
Jonathan R. Haws
Electrical Engineer
Space Dynamics Laboratory
(435) 797-4629
jh...@sdl.usu.edu



From: sqlite-users-boun...@sqlite.org [sqlite-users-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
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 are doing
thousands of queries within a small area, and the db records were also
inserted by area, you may have better luck.

Jim
--
HashBackup: easy onsite and offsite Unix backup
http://sites.google.com/site/hashbackup


On Fri, Oct 29, 2010 at 12:07 PM, Jonathan Haws
<jonathan.h...@sdl.usu.edu>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
> get the database file up and running for an embedded application (we have
> tons of storage space so the size is not a problem).
>
> Here is the schema layout:
>
> CREATE table Dted (dtedkey INTEGER PRIMARY KEY, dted_lat
> INTEGER, dted_lon INTEGER, dted_alt FLOAT);
>
> We lookup dted_alt based on dted_lat and dted_lon.  Here is our SELECT
> statement:
>
> 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 our application.  We were hoping we could run somewhere
> on the order of 500 queries per second and get valid results back.
>
> I am not an SQL expert, but I was reading about indexes that that it is
> best to have a specific index per SELECT.  Since we only have one,
> this is the index I am creating now (it has been creating this index on my
> machine for the past 10 minutes now):
>
> CREATE INDEX idx_ll ON Dted(dted_lat,dted_lon);
>
> Is that a good index for my SELECT?  Will it speed up the accesses?
>
> Any thoughts?
>
>
> Thanks!
> --
> Jonathan
> ___
> 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
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Slow SELECT Statements in Large Database file

[Jonathan Haws]

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.

We had a hash table implementation that did not work very well.  The problem 
with it was that we could only keep a single DTED square in our limited amount 
of memory (this is for an embedded application).  When we had to switch 
squares, the whole process would grind to a halt while it brought in the new 
square to memory and evicted the other one.  From our initial benchmarks with 
SQLite, this problem can be avoided - even though we do have the overhead of a 
huge DB file.

When I create the database, I do make sure that everything is inserted in 
dted_lat order for that exact reason.

Thanks for the help.

--
Jonathan R. Haws
Electrical Engineer
Space Dynamics Laboratory
(435) 797-4629
jh...@sdl.usu.edu



From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Black, Michael (IS) [michael.bla...@ngc.com]
Sent: Friday, October 29, 2010 10:24 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] 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.   
Otherwise it's going to do a lot of paging to get your records.

#1 How many points do you have?
#2 How many points are returned from a select?
#3 Have you considered just building a hash table to do this and skip the 
database?  If you're only doing this one select you don't need the overhead of 
a database.

Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Northrop Grumman Information Systems




From: sqlite-users-boun...@sqlite.org on behalf of Jonathan Haws
Sent: Fri 10/29/2010 11:07 AM
To: sqlite-users@sqlite.org
Subject: EXTERNAL:[sqlite] Slow SELECT Statements in Large Database file



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
get the database file up and running for an embedded application (we have
tons of storage space so the size is not a problem).

Here is the schema layout:

CREATE table Dted (dtedkey INTEGER PRIMARY KEY, dted_lat
INTEGER, dted_lon INTEGER, dted_alt FLOAT);

We lookup dted_alt based on dted_lat and dted_lon.  Here is our SELECT
statement:

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 our application.  We were hoping we could run somewhere
on the order of 500 queries per second and get valid results back.

I am not an SQL expert, but I was reading about indexes that that it is
best to have a specific index per SELECT.  Since we only have one,
this is the index I am creating now (it has been creating this index on my
machine for the past 10 minutes now):

CREATE INDEX idx_ll ON Dted(dted_lat,dted_lon);

Is that a good index for my SELECT?  Will it speed up the accesses?

Any thoughts?


Thanks!
--
Jonathan
___
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] Slow SELECT Statements in Large Database file

[Jonathan Haws]

Yeah, I am letting it run and will check to see how things work once it is 
finished.  I expect things to speed up quite a bit.

--
Jonathan R. Haws
Electrical Engineer
Space Dynamics Laboratory
(435) 797-4629
jh...@sdl.usu.edu



From: sqlite-users-boun...@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:

> 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 our application.  We were hoping we could run somewhere
> on the order of 500 queries per second and get valid results back.
>
> I am not an SQL expert, but I was reading about indexes that that it is
> best to have a specific index per SELECT.  Since we only have one,
> this is the index I am creating now (it has been creating this index on my
> machine for the past 10 minutes now):
>
> CREATE INDEX idx_ll ON Dted(dted_lat,dted_lon);

Yes, that's a good index for that particular SELECT.  It will make that SELECT 
return results in tiny fractions of a second.  And with an 8.7 Gig database it 
will take some time to create the index: perhaps even hours, depending on your 
platform.

Don't worry about it: you seem to be doing the right thing.  Just let it run.

Simon.
___
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] Slow SELECT Statements in Large Database file

[Gabor Grothendieck]

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
> get the database file up and running for an embedded application (we have
> tons of storage space so the size is not a problem).
>
> Here is the schema layout:
>
> CREATE table Dted (dtedkey INTEGER PRIMARY KEY, dted_lat
> INTEGER, dted_lon INTEGER, dted_alt FLOAT);
>
> We lookup dted_alt based on dted_lat and dted_lon.  Here is our SELECT
> statement:
>
> 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 our application.  We were hoping we could run somewhere
> on the order of 500 queries per second and get valid results back.
>
> I am not an SQL expert, but I was reading about indexes that that it is
> best to have a specific index per SELECT.  Since we only have one,
> this is the index I am creating now (it has been creating this index on my
> machine for the past 10 minutes now):
>
> CREATE INDEX idx_ll ON Dted(dted_lat,dted_lon);
>
> Is that a good index for my SELECT?  Will it speed up the accesses?
>
> Any thoughts?
>
>

Google for the spatialite extension.

-- 
Statistics & Software Consulting
GKX Group, GKX Associates Inc.
tel: 1-877-GKX-GROUP
email: ggrothendieck at gmail.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Slow SELECT Statements in Large Database file

[Jim Wilcoxson]

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 are doing
thousands of queries within a small area, and the db records were also
inserted by area, you may have better luck.

Jim
--
HashBackup: easy onsite and offsite Unix backup
http://sites.google.com/site/hashbackup


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
> get the database file up and running for an embedded application (we have
> tons of storage space so the size is not a problem).
>
> Here is the schema layout:
>
> CREATE table Dted (dtedkey INTEGER PRIMARY KEY, dted_lat
> INTEGER, dted_lon INTEGER, dted_alt FLOAT);
>
> We lookup dted_alt based on dted_lat and dted_lon.  Here is our SELECT
> statement:
>
> 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 our application.  We were hoping we could run somewhere
> on the order of 500 queries per second and get valid results back.
>
> I am not an SQL expert, but I was reading about indexes that that it is
> best to have a specific index per SELECT.  Since we only have one,
> this is the index I am creating now (it has been creating this index on my
> machine for the past 10 minutes now):
>
> CREATE INDEX idx_ll ON Dted(dted_lat,dted_lon);
>
> Is that a good index for my SELECT?  Will it speed up the accesses?
>
> Any thoughts?
>
>
> Thanks!
> --
> Jonathan
> ___
> 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] Slow SELECT Statements in Large Database file

[Black, Michael (IS)]

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 records.
 
#1 How many points do you have?
#2 How many points are returned from a select?
#3 Have you considered just building a hash table to do this and skip the 
database?  If you're only doing this one select you don't need the overhead of 
a database.
 
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Northrop Grumman Information Systems
 



From: sqlite-users-boun...@sqlite.org on behalf of Jonathan Haws
Sent: Fri 10/29/2010 11:07 AM
To: sqlite-users@sqlite.org
Subject: EXTERNAL:[sqlite] Slow SELECT Statements in Large Database file



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
get the database file up and running for an embedded application (we have
tons of storage space so the size is not a problem).

Here is the schema layout:

CREATE table Dted (dtedkey INTEGER PRIMARY KEY, dted_lat
INTEGER, dted_lon INTEGER, dted_alt FLOAT);

We lookup dted_alt based on dted_lat and dted_lon.  Here is our SELECT
statement:

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 our application.  We were hoping we could run somewhere
on the order of 500 queries per second and get valid results back.

I am not an SQL expert, but I was reading about indexes that that it is
best to have a specific index per SELECT.  Since we only have one,
this is the index I am creating now (it has been creating this index on my
machine for the past 10 minutes now):

CREATE INDEX idx_ll ON Dted(dted_lat,dted_lon);

Is that a good index for my SELECT?  Will it speed up the accesses?

Any thoughts?


Thanks!
--
Jonathan
___
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] Slow SELECT Statements in Large Database file

[Simon Slavin]

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 our application.  We were hoping we could run somewhere
> on the order of 500 queries per second and get valid results back.
> 
> I am not an SQL expert, but I was reading about indexes that that it is
> best to have a specific index per SELECT.  Since we only have one,
> this is the index I am creating now (it has been creating this index on my
> machine for the past 10 minutes now):
> 
> CREATE INDEX idx_ll ON Dted(dted_lat,dted_lon);

Yes, that's a good index for that particular SELECT.  It will make that SELECT 
return results in tiny fractions of a second.  And with an 8.7 Gig database it 
will take some time to create the index: perhaps even hours, depending on your 
platform.

Don't worry about it: you seem to be doing the right thing.  Just let it run.

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

[sqlite] Slow SELECT Statements in Large Database file

[Jonathan Haws]

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
get the database file up and running for an embedded application (we have
tons of storage space so the size is not a problem).

Here is the schema layout:

CREATE table Dted (dtedkey INTEGER PRIMARY KEY, dted_lat
INTEGER, dted_lon INTEGER, dted_alt FLOAT);

We lookup dted_alt based on dted_lat and dted_lon.  Here is our SELECT
statement:

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 our application.  We were hoping we could run somewhere
on the order of 500 queries per second and get valid results back.

I am not an SQL expert, but I was reading about indexes that that it is
best to have a specific index per SELECT.  Since we only have one,
this is the index I am creating now (it has been creating this index on my
machine for the past 10 minutes now):

CREATE INDEX idx_ll ON Dted(dted_lat,dted_lon);

Is that a good index for my SELECT?  Will it speed up the accesses?

Any thoughts?


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