SQL connections

  • Thread starter Thread starter Gina L. Hernandez
  • Start date Start date
G

Gina L. Hernandez

Hello:

I have a WEB application written in ADO.NET. From this application I open
connections to AS/400 database and my SQL Database, my ADO.NET application
and the databas share the same server, the question is what is more
efficient to open , uses and then close a connection for every stored
procedure I need to execute or open just a connection for everyuser and keep
this connection open until the user log out?. My system is an inventory
system, so once the user is logged in the system, many requirements are
gonna be sent to the server, as WEB requirements and sql requirements
trying to select/update/delete records in my tables.


Thanks for your help
 
From a pure performance standpoint, keeping a connection open will outperform
opening and closing. The issue here is it can be extremely costly in both
memory and licenses to adopt this strategy. It is not as bad in a controlled
desktop application, but can be extremely damaging in an ASP.NET app.

NOTE: the human factor comes into play with all data apps. Humans take some
time to read data, work with it and fire off updates. This amounts to more
time that an open connection sits there doing nothing, even with desktop
applications. As ADO.NET pools connections, underneath the hood, I would
generally side with opening and closing connections rather than leaving one
open the entire time. If you need certain types of locks, you might go the
other direction; otherwise, short lived connections are better.


---

Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************
 
Hi Gina,

I would suggest to close connection as soon as possible. If you worked with
COM-based ADO and compare it's architecture with ADO.NET, then you will see
that .NET moved into disconnected environment. There is no reason to keep
connection opened for long period of time. First of all each connection will
require additional resources from the server. In a case of big number of
users it could be resources consuming. Sine managed provider supports
pooling of the connection, you will not see big performance impact when you
open and close connection for the same user with same connection sting.
 
Back
Top