Re: Closing Connections between Queries

Roger Binns wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> Kyle Stevens wrote:
>   
>> Is it best to 
>> open and close the connection on each query, or should the connection 
>> remain open throughout the life of the application?  
>>     
>
> When you open a connection in SQLite, it parses and loads the schema on
> the first query.  It also maintains a cache (up to 2MB by default).
> This is obviously extra effort, but in these days of billions of
> processor cycles per second is completely negligible.
>
> If you want the least amount of hassle and just want things to work then
> it is best to open and close the connection per query.  The reason is
> that if you try to run a query against a cached schema then you can get
> a schema error.  That is annoying to test for and work around.
>   
If I did what Rich suggested, commit the transaction but leave the 
connection open, wouldn't that prevent any cache schema errors?  Then I 
wouldn't have to worry about opening and closing the connection each time.
>   
>> Also, does the
>> answer differ depending on the DBMS used or whether it is a single user 
>> or multi user environment?
>>     
>
> Networked databases (ie almost all of them) use the resources (disk,
> memory, caching etc) on the server side.  The connection overhead is
> pretty much establishing a tcp connection and doing authentication.
> That latency is still relatively high because it is a serial set of
> steps dependent on external factors like the network.
>   

> In general the best advice is to write things as simply as possible
> (your code is read far more often than it is written) and ensure you
> have extensive testing (do you want to find the bugs or do you want your
> customers to?).  Then profile your code to find the performance
> bottlenecks and address those  - it is almost always nowhere near where
> you think it would be then.  Your extensive tests will help to ensure
> your performance optimizations don't break anything.
>   
Performance isn't my main concern here; but there is no point in taking 
a performance hit if leaving the connection open will be easier anyway.  
For the past couple of weeks, I have been making test methods for all my 
code.  It makes me feel much better.

Thanks for the help.  Between your suggestions and Richards, I am 
feeling very enlightened!