Hello!
Imagine I am trying to introduce a local key-value SQLite database for
caching some data retrieved from a remote server. Key is a character
string, value is a BLOB (and for 50% keys is just NULL). I will specify the
details later.
My database weights a little less than 2 Gbs and contains 130'000 keys.
When I put it on HDD and try to make 10000 queries (extracting values for
10000 different keys) with some additional processing of extracted values,
it takes about 4 seconds on my PC on any run except the first, with maybe
half of that time being the "additional processing" mentioned; even when I
perform every query 3 times in a row (making it 30000 queries), this time
does not change. On the first run, thought, 10000 queries take about 30
seconds!
When I put the file on a USB flash drive, somehow I always get about 45
seconds total time, on either first run and subsequent runs. When the
queries are tripled, the total time is tripled as well (even though the
portions of the file to be read should already be cached when every enuqie
query is repeated 2 more times).
This leads me to the conclusion that 1) The delays are produced by physical
reading of the file, not by searching for a key or returning the value; 2)
file on USB never gets cached (why?? due to some file-mode flags used by
SQLite engine? or it's just a MS Windows 8 issue?)
Now, does anybody know whether it's a known issue for SQLite that the
access is slow when the DB file is not yet cached by OS (I mean, it's
slower that one could expect: we have been previously using a hand-made
engine with which those 10000 queries took just 4 seconds!)? Or maybe
that's an issue specific to something, like, string-based indexes, or this
large databases (approx. 2 Gb), or to something else?
The details:
CREATE TABLE global (
[key] VARCHAR (1024),
value BLOB,
level INTEGER NOT NULL,
original_name VARCHAR (1024),
id INTEGER PRIMARY KEY AUTOINCREMENT,
parent_id REFERENCES global (id)
);
And there are 3 separate indices: by level, key, and parent_id.
So the actual PK is integer, but most queries are like key=something.
I am opening the DB with
sqlite3_open_v2(fdatabase, fdb, 1{ SQLITE_OPEN_READONLY} , nil);
and querying it with
sqlite3_prepare(fdb, 'select value from global where key = ifnull(?,'''')',
-1, fReadValueQuery, ptail); + sqlite3_bind_text + sqlite3_step
+ sqlite3_column_blob
Maksim
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
