Connection Pool or Shared Connection

Question

We are using Petapoco ORM to write the DAL and have some questions about efficient Connection pool usage.

Q1) If the connection gets closed and disposed on every Query or Command (same connection string), Is it still able to use the pool or is it creating a new connection every time?

Q2) How can I tell if connection pool is really working. I see in the Azure Management Portal that my concurrent connection can jump to the 50s... I am skeptical that we have that many running concurrently.

Q3) Please review the following SharedConnectionOpen code, is this "fighting" the pool?

        // Open a connection (can be nested)        
        public void OpenSharedConnection()
        {
            if (_sharedConnectionDepth == 0)
            {

                _sqlConnection = _factory.CreateConnection();
                _sqlConnection.ConnectionString = _connectionString;

                _sqlConnection.Open();

                _sqlConnection = OnConnectionOpened(_sqlConnection);

                if (KeepConnectionAlive)
                    _sharedConnectionDepth++;		// Make sure you call Dispose
            }
            _sharedConnectionDepth++;
        }

Thx in advance,

Uri

Answer 1

see http://blogs.msdn.com/b/sqlazure/archive/2010/05/11/10011247.aspx

Answer 2

Hi,

Closing connections from the application doesn't really close the connection to the database; it remains open on the local machine connection pool. This avoids reopening the connection the next time a database connection is needed. That's why connections remain open from the database standpoint for a long time. To release the connection and make it available in the connection pool you need to close it; so to answer your first question, you need to close the connection often to make it available. However, I am not sure about "disposing" the connection; you should not have to do that.

If you see too many connections opened on the database, it either means that something prevents the connection pool from working (such as a different connection string) or you have many concurrent users.

This link explains how connection pooling works: http://msdn.microsoft.com/en-us/library/8xx3tyca.aspx

To answer your second question, just monitor your connections in SQL Database (dm_exec_connections) in a controlled environment, where you are the only one using the system. If your application uses more connections over time, your code isn't leveraging connection pooling.

To your third question, it's hard to tell without knowing what _factory.CreateConnection() does and without confirming that the connection is the same everytime. Also, try not disposing your connection objects; just close them properly. This link shows the proper way to open and close a connection with the using keyword to ensure the connections get closed: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.aspx

 

---

Herve Roggero, MVP Windows Azure Co-Author: Pro SQL Azure http://www.bluesyntax.net

Answer 3

Hi Herve,

Thanks for taking the time to answer with detail the questions, I will follow your recomendation not to Dispose the object, just close the connection.

If I see the counter decrementing or stable in the management portal, I will post in this thread the results for the community to compare (specially for the Petapoco users).

regards,

Uri