On Thu, Dec 11, 2014 at 6:51 AM, Paul <de...@ukr.net> wrote: > > 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? >
Can you send us a copy of your schema and the STAT tables? You can generate the content we need by running "sqlite3 YOURDATABASE .fullschema" and capturing the output. With that, we can reproduce your problems and perhaps offer some hope of (minor) improvement. But really, the process of opening the database connection does require reading and parsing the entire database schema and then running "SELECT * FROM sqlite_stat1; SELECT * FROM sqlite_stat4;" queries to extract the STAT data. That will all take *some* time. Is there no way that you can open the database connections in advance and having the standing by? > > 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 > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
