Is it ok to leave a sql connection open?

Question

I am receiving data through a com port continuously and doing some decoding. When decoding is done i have to store the results in a sql database. I am thinking since the decoding is done (in a while loop always running) dozens of times per second and data need to be stored to the database dozen of times every second if it is wise to open and close the connection to the sql server in each while loop or just leave it open and continue to write data to the database. First of all is this possible? Secondly if the connection remains open can third party applications or computer access the database at the same time and read data as my programm stores data?

Answer 1

I'd open the connection, go through the loop as many times as needed, then close the connection. Opening and closing is very expensive. But, thanks to Mitch Wheat and Dave Markle, I've learned that connection pooling is done for free in the background with .NET, so the expense should be amortized over all your requests.

Even better, I'd batch the requests in the loop and execute the batch after the loop was done. You only require one network round trip that way.

If you agree with that last bit, I'd make sure that the batch INSERT was done in a transaction context so it could be committed or rolled back as a single unit of work. Isolation and thread safety will matter.

Answer 2

A database supports more than one concurrent connection, so yes it is very feasible in this scenario to leave the DB connection open - you will only lock out others if you have i.e. a long running query that results in row/ table locking. Just close the connection when you are done.

Also consider though that most DBs (i.e. SQL Server) use connection pooling internally, so even though you close a DB connection it just goes back to the pool and is not physically closed - the pool manages the physical DB connections - this results in much better performance, so the impact of opening/closing connections rapidly is reduced.

From MSDN:

Connection pooling reduces the number of times that new connections must be opened. The pooler maintains ownership of the physical connection. It manages connections by keeping alive a set of active connections for each given connection configuration. Whenever a user calls Open on a connection, the pooler looks for an available connection in the pool. If a pooled connection is available, it returns it to the caller instead of opening a new connection. When the application calls Close on the connection, the pooler returns it to the pooled set of active connections instead of closing it. Once the connection is returned to the pool, it is ready to be reused on the next Open call.