IMO, depends on the type of process wanting to make a connection. Anything web service wise is typically stateless, which means any time a new request is made, you have to re-open a new connection. You can probably get away with using server side variables to handle the connections, as I did in ASP days in IIS under Win2k, but I've not done so with PHP/Apache. With IIS, I used to use pooling connections, however, 99% of the time, it was just in-office stuff with a staff of 4, and typically only 5 computers accessing the server. I'd guestimate that during a regular 8 hour shift, server work load time was in the minutes, so not exactly what you'd call heavy use. ;)
If you're using a desktop application, I leave the connection open to the database for the life of the application. Right at the application start, I connect to the database, and when the application is to die, I close it. During the life span, I create tables objects within the procedures/functions I need to do the prepare, or direct SQL queries. On Wed, Jun 19, 2013 at 10:15 AM, Rob Willett <rob.sql...@robertwillett.com>wrote: > Hi, > > My first question as a lurker and read of these groups. Hopefully it's not > too stupid :) > > I've been working through using SQLite on my Mac and have a question on > style and the way to use SQLite databases. > > My application has two parts, a client/PHP side to collect requests from a > user and a C server side program that manipulates large quantities of data, > anything from 10's bytes to 10's of Terabytes. When its manipulating TB's > of data (none of which is in a SQL database) it will run for hours which is > absolutely fine and expected. Just to be clear I do not have a performance > issue with SQLite at all. > > Since the application has to work across numerous operating systems, I'm > treating SQLite as the main way to handle information, I'm not using lock > files, semaphores or any other files as these are all too OS specific. I > hold requests to do work in SQLite in a queue table, I pass information > back to the client side via a SQL database. None of this is too difficult > and I take my inspiration from the manual which says that you can use > SQLite almost as a replacement for a file in some cases. I like the idea of > a simple interface like this and to be honest it seems to work well. > > 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. > > Comments welcomed. > > Thanks, > > Rob > > > > _______________________________________________ > 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
