Connection Pooling | Beginning ASP.NET 2.0 and Databases (Wrox Beginning Guides)

Chapter 11 - Performance

byJohn Kauffman, Fabio Claudio Ferracchiatiet al.?
Wrox Press ?2002

With our timing strategy in place, we can move on. It's well known that connecting to a database can be a slow process, and that connection pools can reduce the length of time it takes. A connection pool is simply a set of connections with like connection strings. The idea is that if a connection is made with one set of attributes, this connection is not immediately closed when the Close() method of the connection object is called. Rather, it's placed in a pool. If another connection is then opened with exactly the same set of connection string parameters, the pooled connection is returned, rather than going to all the work of creating an entirely new connection from scratch. If the system becomes less loaded, this pooled connection will reach a timeout threshold and will be truly be closed. Connection pooling improves performance and facilitates the scalability of an application that accesses data.

OLE DB and Connection Pooling

By default, an OLE DB.NET connection object takes advantage of connection pooling. To be technically correct, connection pooling is implemented in OLE DB using resource pooling, which ties it to a couple of other performance enhancers: automatic transaction enlistment, and a client-side cursor engine. These services are ultimately controlled by the OLE DB Services connection attribute, which has a default value of -1. However, a variety of permutations exist:

Services Provided	OLE DB Services
Connection pooling, transaction enlistment, client-side cursors	-1
Transaction enlistment, client-side cursors	-2
Client-side cursors	-4
Connection pooling, transaction enlistment	-5
Transaction enlistment	-6
No services provided	0

The various permutations associated with the OLE DB Services connection attribute are a little unintuitive, and beyond the scope of the current discussion. For more information, take a look at the MSDN article entitled Overriding Provider Service Defaults, which you'll find at ms-help://MS.VSCC/MS.MSDNVS/oledb/htm/oledboverriding_provider_service_defaults.htm

What's pertinent to our performance discussion is simply that the OLE DB Services connection attribute can be used to disable connection pooling. For best results, this should take place when connections do not need to be reused, meaning that resources should be cleaned up with deliberate immediacy.

SQL Server and Connection Pooling

The developers of SQL Server's .NET data provider were so certain that connection pooling was important that it's the default behavior exhibited by SqlConnection objects. The following example demonstrates this.

Try it Out - Evaluating Connection Pooling using SqlConnection

In Visual Basic .NET, start a new web application project called WXSinglePool.
Using Visual Studio .NET's Toolbox, drag-and-drop a Button (which should be named ButtonTestConnection), a TextBox (which should be named TextBoxNumIterations), a Label (which should be named LabelResults), and a CheckBox (which should be named CheckBoxCloseConnection). The TextBoxNumIterations box will used to determine the number of SqlConnection objects to open during a test.
View the code behind the WebForm1.aspx file by choosing the View | Code menu item.
Since we're using the SQL Server .NET data provider, it makes sense to add an Imports declaration to the top of our source code file:
```
 Imports System.Data.SqlClient 
```

Finally, add the following event handler to help us evaluate how SqlConnection objects utilize pooling:

 Private Sub ButtonTestConnection_Click(ByVal sender As System.Object, _                ByVal e As System.EventArgs) Handles ButtonTestConnection.Click   Dim numTests As Integer = Int32.Parse(TextBoxNumIterations.Text)   Dim count As Integer = 0   Dim connString As String   Dim startTime = DateTime.Now   Dim howLong As TimeSpan = New TimeSpan()   Try     connString = "server=localhost;database=northwind;" & _                  "uid=sa;pwd=;"     startTime = DateTime.Now     For count = 1 To numTests       Dim conn As New SqlConnection(connString)       conn.Open()       If CheckBoxCloseConnection.Checked Then         conn.Close()       End If     Next     howLong = DateTime.Now.Subtract(startTime)     LabelResults.Text = String.Format("{0} ms", howLong.TotalMilliseconds)   Catch ex As System.InvalidOperationException     howLong = DateTime.Now.Subtract(startTime)     GC.Collect()     LabelResults.Text = String.Format("Pool count exceeded ({0} ms): {1}", _                                      howLong.TotalMilliseconds, count)   Catch ex As Exception     LabelResults.Text = String.Format("Exception: " & ex.ToString())   End Try End Sub

How It Works

The previous code sample opens and closes an SqlConnection object repeatedly. On the system under test, the time taken to open and close one million connections is a little less than thirty-five seconds, which translates to 35 microseconds per connection! Clearly there is some kind of pooling going on.

By default, the SqlConnection class's Pooling connection string attribute is set to true, but it can be set to false, thus disabling pooling. An example of a connection string with connection pooling disabled would be as follows:

         connString = "server=localhost;database=northwind;" & _                      "uid=sa;pwd=;pooling=false"

After disabling pooling, opening and closing a thousand database connections takes approximately three seconds (3 ms per connection). This is much longer than the pooling case. Also, this number was generated with the ASP.NET application and SQL Server database residing on the same machine. If the SQL Server database had resided on a remote host, the performance difference between using and not using connection pools would have been amplified further still.

In our code above, a CheckBox control is used to disable the closing of the connection:

            If CheckBoxCloseConnection.Checked Then              conn.Close()            End If

The purpose of disabling the closing of the connection is to demonstrate that connection pooling has an upper bound with respect to the number of connections pooled. When this number is exceeded, an InvalidOperationException is raised and the following error text is generated (as the Message property of the exception):

 Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.

The code that handles this exception inside our form is as follows:

       Catch ex As System.InvalidOperationException         howLong = DateTime.Now.Subtract(startTime)         GC.Collect()         LabelResults.Text = String.Format("Pool count exceeded ({0} ms): {1}", _                                           howLong.TotalMilliseconds, count)

Note that garbage collection is initiated here using the GC class's Collect() method. This step is performed in order to ensure that all of the orphaned database connections are closed; the garbage collector closes these connections as part of object finalization.

The previous error-handling code snippet can be used to demonstrate that the upper bound on connections in the connection pool is 100, but it's not quite as simple as that. The upper bound of 100 applies to the number of pooled connections per different connection string. A connection string is 'different' if it contains a different host, database catalog, user, or security type. For example, the following two connection strings are different because their database attributes are associated with different values:

         conn1 = "server=localhost; database=northwind; uid=sa; pwd=;"         conn2 = "server=localhost; database=nobreezeatall; uid=sa; pwd=;"

Sometimes, development projects can inadvertently create different connection strings, and therefore inflate the number of pooled connections. To demonstrate this, consider a team of three developers, each of whom uses the same basic connection string, except that they use different values for the Connection Timeout connection attribute. This would cause three separate connection pools to be created, and each such pool would have a default maximum of 100 connections. The worst-case scenario would be three hundred connections. Scaling up, if twenty developers each accessed five different databases, and each developer used a different timeout setting, the worst case would get worse very quickly: 20 developers times five databases times 100 in the pool means up to ten thousand connections, and an enormous drain on resources.

Try It Out - Creating Separate Connection Pools

The WXMultiPool application, which is available for download from www.wrox.com along with the rest of the code for this book, demonstrates a variety of connection options that can result in the creation of separate connection pools.

click to expand

This ASP.NET form contains four RadioButton instances that correspond to specifying different values for the following connection attributes: Packet Size, Connection Timeout, Connection Lifetime, and Application Name; and a Test Connection Button. Feel free to have a look around the code before we discuss this example.

How It Works

When the Test Connection button is clicked, SQL Server connections are opened but not closed. When the upper bound is reached with respect to the number of connections pooled, the application ceases running. In order to demonstrate that the esoteric connection attributes discussed above really can cause multiple connection pools, the application behaves differently for even iterations and odd iterations. When the Connection Timeout Differs box is checked, for example, odd iterations are given a timeout value of 15, while even ones get a value of 16. The following table shows a full list of these different settings:

	Odd Iteration	Even Iteration
Packet Size	8,192	16,384
Connection Timeout	15	16
Connection Lifetime	1	2
Application Name	Name2	Name1

The WXMultiPool application demonstrates that differences in a variety of connection attributes cause multiple connection pools to be used, leading to the consumption of a large amount of resources. Furthermore, when each area of the program is using a slightly different connection string, the connection pools themselves are not being used efficiently.

Other Pooling Attributes

The control that it's possible to exert over connection pooling extends to more than just switching it on and off; it's also possible to specify how many connections there should be in a pool. The maximum number of connections in a given pool can be set using the Max Pool Size connection attribute, while the minimum number of connections in a given pool can be set using the Min Pool Size connection attribute. The defaults for these attributes are 100 and zero respectively.

In an environment where a particular connection will be used a lot, the Min Pool Size should be set to a number larger than its default value. Once the minimum number of connections has been created, that number of connections will then remain for the lifetime of the application. Raising Max Pool Size can also aid performance, while lowering it can be used to enforce a connection restriction. If the range of options, and the difficulty of determining what's best for your application, is beginning to sound dizzying, fear not: there is a mechanism that can be used to determine the peak number of connections used, the current number of pooled connections, and the current number of non-pooled connections. That mechanism is performance counters, and we'll be seeing all about those shortly.

While we're on the subject of connection pool attributes, though, the WXMultiPool application introduced Connection Lifetime. Each time a connection is returned to a pool, the Connection Lifetime value is compared against the number of seconds the connection has been alive. If the connection has been alive for longer than the Connection Lifetime, the connection is removed from the pool. Note that a value of zero, which is the default for this attribute, corresponds to the longest possible timeout, rather than the shortest.

Finally, if you're using the SQL Server .NET data provider with SQL Server 7.0 (or MSDE), you might need to know about the Connection Reset connection attribute. By default, this is set to true, indicating that the connection state is reset each time a connection is returned to the pool. To understand the implications of this, consider the case in which a connection is initially set to the Northwind database, but changed while in use to the Pubs database. In this scenario, returning the connection to the pool would cause the connection to be reset, and therefore the database to be reset to Northwind. The implication of this reset is a roundtrip to the database that you can avoid by setting the Connection Reset attribute to false, and remembering that you'll need to program for this change in behavior.