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
