8

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?

redfrogsbinary
  • 619
  • 5
  • 13
  • 26
  • And another thing. Can i open and close a connection several times a sec or there is a timeout or something like that preventing such often use? – redfrogsbinary Aug 16 '11 at 01:16
  • Program will be running 24/7. This means never closing the connection – redfrogsbinary Aug 16 '11 at 01:19
  • 2
    http://msdn.microsoft.com/en-us/library/8xx3tyca.aspx – Mitch Wheat Aug 16 '11 at 01:21
  • 1
    Personally, I open/close to avoid confusing transfer in scope/lifetime -- even if it might not be needed. If there is a perceived performance issue, benchmark (as others pointed out, pooling essentially negates this). Make sure to manage *transactions*, however. Even if the DB is kept open, remember to *commit* as required. Otherwise data may never be commited or locks may never be released :) –  Aug 16 '11 at 01:27

4 Answers4

11

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.

BrokenGlass
  • 158,293
  • 28
  • 286
  • 335
3

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.

duffymo
  • 305,152
  • 44
  • 369
  • 561
  • 2
    "Opening and closing is very expensive." - Opening first time is; after that, provided connection string is the same, connection pooling should kick in. But as you say, batch requests. – Mitch Wheat Aug 16 '11 at 01:19
  • loop never ends. This is what keeps com port open – redfrogsbinary Aug 16 '11 at 01:21
  • 2
    Although opening and closing *is* expensive, he's using C#, which probably means that he's got connection pooling working for him in the background, which just about completely mitigates this issue. – Dave Markle Aug 16 '11 at 01:22
  • 1
    Assuming that a pool is used, Mitch. No initial indication that one is in play. I agree if you see one. I didn't realize that pooling came along for free in .NET. If that's the case, I'll amend my answer. – duffymo Aug 16 '11 at 01:24
  • pooling is engaged automatically? – redfrogsbinary Aug 16 '11 at 01:33
0

Actually you must explicitly close SqlConnection.Open()

From MSDN, "Note If the SqlConnection goes out of scope, it is not closed. Therefore, you must explicitly close the connection by calling Close or Dispose."

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.open(v=vs.71).aspx

Cheers!

Stephan Unrau
  • 360
  • 3
  • 5
0

I personally would open and close the connection each time. You should be protecting your code in a way that any exception will ultimately close your connection. Will your app be the only application talking to this database?

  • 1
    In that case I would cautious with holding onto connections any longer than necessary. –  Aug 16 '11 at 01:25
  • in my case eachtime would be in the loop – redfrogsbinary Aug 16 '11 at 01:31
  • correct. I would only keep the connection open when there is something left to read or write. If you are waiting for some other process/step to finish, I would close the connection. –  Aug 16 '11 at 01:31