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: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Tim Morton [t...@preservedwords.com] Sent: Tuesday, March 20, 2012 9:21 AM To: sqlite-users@sqlite.org 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 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
