On 19 Jun 2013, at 3:15pm, Rob Willett <rob.sql...@robertwillett.com> wrote:
> Now to the hub (excuse the really bad pun) of my questions, within my service > side application, I'm finding that I'm constantly opening and closing the > same SQLite database, inserting data, reading data, updating date over many > hours. The total number of interactions is quite small, perhaps a few hundred > over the course of the application run which could take hours to run. > > I'm not bothered over the file i/o per se. nor about the amount of data > written to the database as its very small but wanted to validate that this > approach of opening and closing the database as close to the necessary > transaction as possible is an appropriate 'style'. As I do this I'll open and > close the SQLite database probably a few hundred times in the course of a run. > > My feeling is that opening and keeping open the SQLite database for any time > longer than necessary at all is bad, since my PHP might want to update things. Nice description. In some of my uses I have a setup similar to yours in many ways. The back end to my web-facing services is a small PHP file on my web server which acts as a SQLite3 shim. Requests are passed to it in JSON and it answers in JSON. Some web-facing systems (in JavaScript) and other applications (which generate their own HTTP POST requests) pass it individual SQLite commands and it opens the database, does the command, then closes the database again. Because ... well, that’s all it can really do given the structure of what PHP on a browser can do. So yes, my logs can show my PHP shim being called hundreds of times a minute. But it does the job fine: I’ve never traced down any fault down to it being called so often. And, of course, the server has Apache, PHP, the shim file and many of my SQLite databases permanently in cache, so the whole thing runs pretty quickly. As you write, it does seem to be terribly inefficient. But we’re bound by the nature of how HTTP requests work. You can’t really improve on it without implementing something that serves SQLite over TCP/IP. Or maybe implement it as an Apache module. And it’s so much more convenient to be able to write my code in PHP and know it will run on any Apache platform and can be maintained (should I die) by anyone familiar with PHP programming and HTTP requests. I’ll worry about it more when my server CPU starts heading towards 100%. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
