On 20 Mar 2012, at 2:21pm, Tim Morton <[email protected]> wrote:
> 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. Thank you for your detailed description which helps a lot in considering your situation. > My questions, > > Does table creation order affect the speed of access? These things depend mostly on your hardware. Windows deals much better with defragmented files than fragmented files. A lot of its speed comes from assumptions like if you're looking at sector s, you're shortly going to want sector s+1. To speed up your situation, after your database file has been written, execute a VACUUM command in SQL then (if you're running Windows) defragment your hard disk. If the above does not give you fast enough access, then it may be possible to speed things up still further but writing one TABLE then the other. You can do this when you originally make the file, or you can use the sqlite shell tool to .dump the file to SQL commands and .read the SQL commands back in to form another database file. After doing this, once again, if you're running Windows do another defragment. I don't expect that to make much difference but it might be worth trying. You should also look at the PRAGMA command Michael recommended. > 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? Your TABLE definition is as efficient as it can be. Your most efficient reading code in each situation is SELECT id,topics FROM dictionary Any other optimization can't be done inside the SQL command. Simon. _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
