You may also want to try pragma cache_size and bump it up a LOT just to see what happens to your timings.
Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems ________________________________ From: [email protected] [[email protected]] on behalf of Tim Morton [[email protected]] Sent: Tuesday, March 20, 2012 9:44 AM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] Optimize Table Access Efficiency Thanks for the reply, "Lengthy" varies according to the system, but from a fresh, initial start on this one it can be 15 to 20 seconds. The system cache speeds up subsequent starts to around 3-4 seconds. As mentioned in the post, I have two tables, one for topics and one for definitions. I read the topics table and get the index and then use it to get the definition. The one thing I didn't do is populate the two tables on creation separately. They both are populated in the same "for loop." I'll try that, but with my previous texts I could always get better speed with the topics in a separate DB on a fresh start. Tim On 3/20/2012 10:26 AM, Black, Michael (IS) wrote: > Try creating 2 tables, one for topics, one for definitions. > > > > Then insert all the topics at once followed by all the definitions. > > That should give you the same disk layout as two databases. > > > > And you don't say what "lengthy" means. > > > > Michael D. Black > > Senior Scientist > > Advanced Analytics Directorate > > Advanced GEOINT Solutions Operating Unit > > Northrop Grumman Information Systems > > ________________________________ > From: [email protected] [[email protected]] on > behalf of Tim Morton [[email protected]] > Sent: Tuesday, March 20, 2012 9:21 AM > To: [email protected] > Subject: EXT :[sqlite] Optimize Table Access Efficiency > > Greetings, > > My app reads dozens of SQLite databases ranging in size from 1MB to > 100MB with a simple table structure like, > > "create table dictionary(id INTEGER PRIMARY KEY, topics, definition)" > > On startup the app reads all the databases to extract the "topics" > column data. With the DB configured as above extracting the topic data > is a lengthy process. It seems the whole DB file is being read to just > access the relatively small "topics" column. > > If I add another table with just the topics data, > > "create table dictionary(id INTEGER PRIMARY KEY, topics)" > > access is quicker. However, if I make another DB file with just the > topics column, access is much quicker. I don't really want to use two > files to access a single DB. > > Also, the DB is only read from; not written to by the app. > > My questions, > > Does table creation order affect the speed of access? > > Is there a way to read only the part of the file that has the queried > table/column thus saving time and hard dive grinding? > > Is it possible to read a small table/column from a huge file nearly as > quick as the small table alone from a small file? And if so, how? > > Thanks, > > _______________________________________________ > sqlite-users mailing list > [email protected] > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ > sqlite-users mailing list > [email protected] > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
