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