Should i keep a database connection open?

[tjoppi]

Hi

I am currently working on a project where we have various measuring stations sending data to a central analysis machine. We decided to shift to SQL server as our permanent storage back-end.

The question is this: should the measuring stations maintain a constant connection to the database or should they connect/disconnect as necessary?

Keeping the connection open saves on the ~300ms connection time and with possible multiple consecutive db access this could become a problem. Also if I keep the connection open I have no way of knowing when the station loses connection with the DB until I actually try to interact with it.

So do I keep the connection open with the probable logic overhead of establishing whether the station is connected and reconnecting if necessary or do I simply open and close the connection as needed, obviating the need for testing, but incurring a ~300ms time penalty on each DB operation?

I look forward to your opinions and advice.

Tjoppi 

Message Edited by tjoppi on 02-24-2010 05:20 AM

[dan_u]

Hi Tjoppi

How often do you access the SQL database? If you write/read every second I would definitely keep the connection open. If you only access the DB every hour I would probably close the connection.

In most cases I would tend to keep the connection open as long as your application runs.

Just my 2c.

Daniel

[tjoppi]

---

dan_u wrote:

How often do you access the SQL database? 

---

The DB receives data from multiple connections about every 2 seconds although some of these connections only send data once every 23 seconds. 

[dan_u · Accepted solution]

When sending data every 23 seconds I would keep the DB connection open. As you said, you might have to do error handling when the write task fails due to a broken DB connection.

[RENN]

Great!!! found that i can save some 300 - 800ms if i keep the connection open.