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 > 370000 and -1110000. > > 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