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