2

Say you are developing a desktop application that extensively uses a database, should you have one handle for the database that the entire app references? Or should you open a connection when you need data, get the data, and then close the connection when data isn't necessary.

c-o-d
  • 247
  • 1
  • 10
  • You should open a connection when you need data, get the data, and then close the connection. You can use a connection pool to make managing the connections a bit easier. – Gilbert Le Blanc Mar 03 '14 at 00:28
  • 1
    @GilbertLeBlanc Can a connection pool run out of connections to give? – c-o-d Mar 03 '14 at 00:42
  • Yes, it can run out of connections. It is unlikely, but possible. – Adam Zuckerman Mar 03 '14 at 03:14
  • Keep in mind that having a single connection will prevent you from running queries in multiple threads. Also - opening a new connection when you need to make a db call is a safer/easier pattern, imo; measure a performance hit of opening/closing connections and make a decision whether it actually impacts perceived performance. – Evgeni Mar 03 '14 at 17:08

1 Answers1

6

You have already listed the two major schools of thought about database connections. Each has positive and negative aspects.

Open a connection and leave it open for the duration of the application

Positive: This works better in a fully connected environment (desktop or server application primarily). You limit the number of connections to your database to a minimum. You don't have the overhead of making the connection and shutting it down every time you want to execute a query.

Negative: This doesn't work well in a disconnected environment or one where the connection is likely to be severed midstream frequently.

Open a connection long enough to do what you need

Positive: This works best in an environment where you are needing data infrequently. You free up a connection for another process in a heavily utilized server. If you get disconnected midstream, just restart the query process with a different connection.

Negative: There is an overhead for making a connection to the database every time. It gets worse when you have SSL and/or VPN in addition to the connection. Database connection pooling can help with this. You have to be aware that INSERTing or UPDATEing data can put you in a race condition with another user/process. It is easy to mitigate, but will require additional design and coding steps.

For a desktop application, I would normally recommend using an open connection. You will still have to have error handling in place for when the connection fails (someone restarts the database on you).

All of the negatives I have listed can be mitigated or limited somewhat by designing your application with error situations in mind (e.g., before you execute a query, you insure the connection is still open).

Adam Zuckerman
  • 3,715
  • 1
  • 19
  • 27