What If I leave Sql Connection Opened ?
Question
Thursday, April 7, 2011 6:47 PM
Hi All,
I've been thinking, it's clear to me that sql connection must be closed after each transaction, but my question is Why?, I don't think resources are a problem considering cpu capacitities nowadays.
I am developping a system for 40 or 50 concurrent users, and for me it's like a big restroom with a lot of doors, isn't it easier to left the doors opened so people can walk in and out without the loss of time of opening the door every time they want to get in or out?
It looks more logical to me to left the door open instead of openning and closing, I would appreciate any comment about it.
Regards.
G.Waters
All replies (5)
Friday, April 8, 2011 7:29 AM ✅Answered
Ok Chirag, thanks for your response, according to your answer, that means that sql will open 50 connections in the pool if 50 users are connected and make a transaction at the same time? G.Waters
Hi George Waters,
Pooled connections are managed by ADO.NET not SQL Server. Connection pooling is an optimization technique used by ADO.NET to minimize the cost of opening connections.
When a connection is first opened, a connection pool is created based on the connection string. After we close a connection in .NET, the connect returns into the pool. When we open another connection with the same connection string, the pooler looks to see if there is an available connection in the pool. If a pooled connection is available, it returns it to the caller instead of opening a new connection.
It’s recommended to call Close or Dispose to close the connections or open the connections inside of a using block. In this way, the connections will be returned to the pool for future reuse.
For more information, please refer to the documents below:
SQL Server Connection Pooling (ADO.NET)
http://msdn.microsoft.com/en-us/library/8xx3tyca.aspx
SqlConnection Class
http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.aspx
Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
Friday, April 8, 2011 2:12 PM ✅Answered
Ok Chirag, thanks for your response, according to your answer, that means that sql will open 50 connections in the pool if 50 users are connected and make a transaction at the same time? G.Waters
In addition to Jian Kang's excellent reply I should add
Connection pooling is based on uniqueness of the connection string, so if you have two client connectig to the SQL Server with slightly different connection string two connection pool will be created. Also, Beauty of the connection pooling mechanism is that if you have 50 concurrent users; performing transaction with your SQL server database and as I said earlier if you open connection late and close it early; there is a possiblity that same connection object may be reused by another client connecting to your database. This makes application highly scalable.
I must add pooling mechanism is maintained at the client (in case of web application on a web server) so in a web appplication (i.e. asp.net web application) it is highly desirable. (that's why it is enabled by default unless you explictly turn it off in connection string by specifying pooling =false)
However, in case of a windows forms application (desktop app) depending upon the requirements pooling may not be desirable (sometime each client may have different connection string) and in those cases you can open and hold connection for a longer time.
Thursday, April 7, 2011 7:20 PM
I don't think resources are a problem considering cpu capacitities nowadays.
You should open a connection immediately before you need to access the database and then close it as soon as you are done accessing the database. Connections hold open valuable resources to the database, consume memory, and can lock data that could cause other queries to slow down. So it's best to open connections late and close them as early as possible.
By closing the connection, if you have connection pooling enabled (by default all .net application has pooling enabled) you return your connection object back to the pool so that it can be reused. Also, pooled connection object most of the time skips authentication (SSPI context) with SQL Server
Here is the documenation that backs my advice
http://msdn.microsoft.com/en-us/magazine/cc163799.aspx#S2
http://www.wintellect.com/ARTICLES/ADO%20NET%20CONNECTION.PDF
Friday, April 8, 2011 4:21 AM
Ok Chirag, thanks for your response, according to your answer, that means that sql will open 50 connections in the pool if 50 users are connected and make a transaction at the same time?G.Waters
Friday, April 8, 2011 4:10 PM
Hello G.Waters,Ok Chirag, thanks for your response, according to your answer, that means that sql will open 50 connections in the pool if 50 users are connected and make a transaction at the same time? G.Waters
Hello G.Waters,
Transactions are sequences of actions that need to either succeed or fail as a group.Usually, if a transaction fails, there is a rollback for every successful change that happened since the beginning of the transaction.
The worst scenario would happen if your 50 connections are trying to update the same row in the same table.You have to be careful with the isolation level for the transactions : i would used IsolationLevel.Serializable which seems to me the best one to prevent any update/insert, but i don't know whether it could generate phantom reads.But i think that Chirag and Jian are the most suitable persons to write about transactions,locks and phantom rows ( i know my limits )
http://msdn.microsoft.com/en-us/library/system.data.isolationlevel.aspx
i give the link towards the Transact-SQL which seems to me more complete
http://msdn.microsoft.com/en-us/library/ms173763.aspx
For Chirag and Jian, i have always thought that a connection is the most expensive operation, it is why i let my connection open all the needed time for the treatement ( that's to say until the whole select/update/insert/delete are done on my database, i don't like using especially when i am working with blocks try/catch/finally ). Mainly, my applications are using Windows Forms , so there is connection form at the begin of the application and the close of the connection is done when the application is closing.With an exception : if the user wants to change the SQL Server instance, i close the open connection before trying to open a new one.The rare times i need to connect to 2 different instances, it is to restore a backup done on another instance or to copy data from an instance to another one ( as i am using SMO , i always desactivate the pooling and i forbid the disconnection until the end of the operation ).
Have a nice day
Mark Post as helpful if it provides any help.Otherwise,leave it as it is.