On 2014/12/11 13:51, Paul wrote:
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.
/Snipped for brevity/
Hi Paul,
You seem to know your way around systems so I will not waste time on details. The core of the problem is that it takes time to open
a database and file and extra cpu cycles because upon opening an SQLite database much cpu and I/O time is spent reading and
interpreting the enitre schema (which sound complex even though the actual data might be small) and then checking for hot-journals,
opening accompanying file objects (possibly WAL journals etc.) and some basic maintenance. It then loads the stat tables and not
only read the data but use it to set up certain Query planner adjustments which eats a few more cycles (This is something I think
happen on startup, but I might be wrong). Also, I think start-up routines is surely not an area of waste but probably not a great
focus of fine-tuning performance and optimizations (Richard or Dan might shed more light if I am wrong about this). Ether way, once
it is open, the speed is lightning quick, as you have noticed. The point being: It is the opening that eats the cpu time.
Next point is that you cannot do much about that in terms of your explanation of how you access data, and I won't try to dissuade
you from the way you use it. One thing that I notice as a definitive possibility is simply dropping all stat tables from your system
and at least saving those reading and adjusting steps. The reason why I suggest this is that you have already done the research and
noticed the time degradation due to it, but more importantly, there is no need.
The stat tables help the Query planner (NGQP as it is officially known these days) to make decisions on making queries on large
datasets a bit faster (sometimes a LOT faster). You however do not have any large datasets or performance-bending queries, you have
no need for this, your need is more to save those cycles at file-open time. You need something we do not often see in database
designs: Quantity over quality - and dropping the stat tables (and the maintenance routines causing them to exist) should do you a
favour.
Best of luck with the implementation!
Ryan
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
