Hello.
In my specific case I need to open database as fast as possible.
Usual working cycle: open -> select small data set -> close.
It is irrelevant how much time it takes to open database when
data is being added or updated, since it happens not too often.
But for selects it's a different story, selects are frequent.
Database structure is pretty complex, but I would say not too much.
21 tables (including sqlite_stat1 and sqlite_stat4) and 11 indices.
For me, it takes 1.2 ms to read database structure. And the process
of reading database structure is strictly CPU bound. When I run
profiler I see 90% of time is spent in sqlite3InitOne() function and
64% inside of loadStatTbl() that is being called by sqlite3InitOne().
Times are measured performing hundred thousands of cycles:
OPEN -> SELECT -> CLOSE
This procdure is not I/O bound, database is small (2MiB) and easily
fits in the file system cache. I have double-checked with iostat.
Weird thing is that when I drop either sqlite_stat1 or sqlite_stat4
or both of them, average time drops from 1.2 ms to 0.4 ms. Why is this
so expensive to read those tables?
I understand, that having them is a must for a decent performance.
In my specific case I have millions of individual database files.
This is one, among other reasons that I can't keep them open all the time.
Just too many of them. These databases are being opened frequently.
Let's say 500 times per second. In most cases, just to query a single row.
Ironically, querying takes only a handful of microseconds, and most
CPU time is spent reading same database structure over and over again.
Can you please make some advice, what can be done to reduce this overhead?
Even reducing it on the account of deleting stat tables, that I do not
consider an option at all, is not enough. 400 microseconds for database
initialization is a lot, if it takes only 5 microseconds to query data.
What specifically I mean by saying initialization:
1) sqlite3_open(...);
2) sqlite3_exec(..." PRAGMA page_size = 4096; "...);
3) sqlite3_exec(..." PRAGMA temp_store = MEMORY; "...);
4) sqlite3_exec(..." PRAGMA cache_size = 10000; "...);
5) sqlite3_exec(..." PRAGMA foreign_keys = ON; "...);
6) sqlite3_exec(..." PRAGMA synchronous = NORMAL; "...);
Sequence is actually irrelevant. And if I skip all the pragmas,
initialization time will be delayed until the first query, so I guess
there is nothing specific about these pragmas.
Thanks,
Paul
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
