Stephen, Simon, I'm not worried about the CPU load at all, it was purely a question of style. Stephan has talked about keeping the connections open for the lifetime of a desktop connection, Simon has talked about closing and opening connections many, many times during the course of a transaction. My model is more towards Simon's.
Part of this model is due to the backend process taking hours to run. I wanted the web user to kick this off and come back when it's finished (possible tomorrow). It didn't feel write to keep the database connection open as other users would/could/might want to add stuff to the queue to be run. The fact I have had two replies, and thank you both for writing back, but have very different viewpoints and experiences makes me think that my model isn't bad for my particular use case. Clearly other people will have different experiences and in different situations I would do it differently. On another note, I have been impressed with the quality of replies on this list. I listen into other lists on other technologies and the quality and standard of 'stuff' here is very high. Not seen very many "My database don't work, fix it for me" type mails here which is great. Some of the detail people go into is wonderful and I've learnt an awful lot just reading replies. Best wishes, Rob. On 19 Jun 2013, at 15:35, Simon Slavin <slav...@bigfraud.org> wrote: > > 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 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
