Hi Ryan, thanks for reply. > > 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 >
Thanks for confirmation of my mental model of internals of sqlite :) Sqlite does all the preparations needed for most optimal performance. And I agree that this is the best choice in overwhelming number of cases. And my case is very very specific. I have yet to try and test if dropping stat tables worth the effort. Some databases in fact can grow pretty big, up to few hundred of megabytes. It is yet unknown how optimal will they perform. But so far, ignoring the CPU overhead, we have a big gain in a disk performance area. Sqlite performs much much better than original storage. Best regards, Paul _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
