Re: Closing Connections between Queries

-----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.

> 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.

Roger
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFG7gVEmOOfHg372QQRAlOfAJwOUMxnBYCgHFLvY/nJ+VQoTJAcEACgo8ZQ
mgKJKOQLr0UwYac6J9CN9FI=
=kDv4
-----END PGP SIGNATURE-----