Member Avatar for SheSaidImaPregy

What is better to do, and if you could, please include the pros and cons along with your opinions:

1) Open the connection to the database as late as possible and end it as early as possible, and do this a total of 5 times in one page_load.

2) Open the connection to the database as late as possible and KEEP it OPEN until the last SQL Query has been retreieved, then close it.

3) Split the connections; Keep one Connection open for 2 or 3 query executes and then close, then open another and execute the remaining.

I believe number 2 is the best, as if 100 people access the page at once, that would create 100 connections in the matter of seconds, rather than option one of creating 500 connections in the matter of seconds. But does leaving the connections open use up more memory and drain more performance than just having 500 connections? Or does it really matter? Let me know.

  • 3 Contributors
  • 3 Replies
  • 814 Views
  • 19 Hours Discussion Span
  • Latest Post Latest Post by SheSaidImaPregy

Dani AI

Generated

Short answer: open a connection as late as possible and close/dispose it as soon as the logical unit of work is finished. Rely on ADO.NET connection pooling rather than holding physical connections open across requests or user sessions. That gives the best balance of scalability and low latency while avoiding “max pool size” exhaustion or stale connections. (learn.microsoft.com)

When the queries are part of the same atomic operation (same transaction, need the same session state or temp tables) use a single connection for those queries and close it at the end of that operation. If the queries are independent, open/execute/close for each — the pool will reuse underlying physical connections so the cost is small. This keeps code simpler and avoids problems that happen when connections are held for long periods. (This expands on and : opening is costly in raw terms, but pooling changes the practical tradeoffs; keep one connection per logical work unit.) (learn.microsoft.com)

Example pattern (C#): 

using (var conn = new SqlConnection(connString))
{
  conn.Open();
  using (var cmd = new SqlCommand(sql1, conn))
  {
    // Execute first command
  }
  using (var cmd = new SqlCommand(sql2, conn))
  {
    // Execute second command
  }
} // Dispose/Close returns connection to pool

Use CommandBehavior.CloseConnection with readers if you hand a reader off, and always Dispose/Close instead of relying on finalizers. Closing/Dispose typically returns the connection to the pool; if pooling is disabled it actually closes the physical connection. (docs.dndocs.com)

Troubleshooting: if you hit timeouts or “Max pool size” errors, look for leaked connections (missing Dispose/using), monitor ADO.NET connection counters (NumberOfPooledConnections, NumberOfReclaimedConnections) and consider ClearAllPools during maintenance operations. Enable pool performance counters to get visibility. (learn.microsoft.com)

Recommended Answers

All 3 Replies

Member Avatar for Ancient Dragon

Opening a database is expensive in terms of both time and resources. So the fewer times you have to open it the better performace your program will achieve. I would opt for option #2. Of course one problem is that you may need to tell the database server to flush the data to disk often. But a good database server will probably do that anyway. The programs I worked with each user had his/her own connection, for security reasons. The connection was opened when the user logged onto the application program and was not closed until the user logged off. There was no more than 8 hours between logon and logoff (one 8 hour shift). One problem we had to overcome was accidental dropped connections do to network down or server down.

Member Avatar for Infarction

Option 2, closing after the last query for the page load (which may be automatic anyways, when everything is GC'ed). The overhead of opening and closing the connections for a single load will always be more than keeping an idle connection for processing 1 request. If you want to keep it around longer than that, there may be security ramifications, as AD suggested.

Member Avatar for SheSaidImaPregy

Great, thought so. I've always done option 2 but had to make sure. Thank you!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.