Connection Pooling

Chapter 1 covered some of the issues that face traditional client/server applications. One of the most significant problems for systems with a large number of relatively low-volume clients is the overhead needed to acquire a limited resource. Database connections are an ideal example: They are finite in number and take a measurable amount of time to create. Without some pooling mechanism to reuse and share connections, the time needed to create a connection for each client request will definitely create a bottleneck.

You could try to address this issue by creating a dedicated database component and then using the pooling features available with COM+. Alternatively, you could try to create a server object that manually creates a pool of connections and hands them out to clients. With a fair bit of effort, both of these approaches could work. Fortunately, Microsoft and other database vendors usually provide an alternative: connection pooling built into the data access technology.

In the case of the managed OLE DB or ODBC .NET provider, you rely on the session pooling mechanism of OLE DB or the connection pooling resources of the ODBC driver manager. Both of these tasks are handled transparently and require no additional settings or extra work. In the case of OLE DB drivers, there might be more connection string settings you can use to configure pool sizes and other variables. (Consult the documentation included with the database product.) For ODBC connection pooling, you can configure some options from the Data Sources window in Control Panel. In both cases, a wealth of information is available online in the Microsoft MSDN Library.

The .NET providers for SQL Server and Oracle also provide their own built-in connection pooling, which doesn't rely on any other standard (such as COM+). This connection pooling is automatically enabled by default and, as discussed in Chapter 9, is quite a bit better than the connection pooling available with the OLE DB and ODBC .NET providers because it enables you to enforce connection maximums. Maximums ensure that the system remains reliable even under a heavy client load. To achieve the same benefits with the OLE DB provider, you need to disable connection pooling and implement a custom object pooling system using COM+.

You can customize connection pooling for the SQL Server and Oracle providers using the connection string settings detailed in Table 12-1.

Table 12-1. SQL Server and Oracle Connection Pooling Settings 

Setting

Description

Connection Lifetime

Specifies the maximum amount of time that a connection can live in the pool (in seconds). Every time a connection is returned to the pool, its creation time is compared against this value. If it is older than the specified lifetime, it is destroyed. The default is 0, which specifies that all connections are retained for the life of the process. The Connection Lifetime setting proves useful when you need to recycle a large number of connections simultaneously, such as when you want to balance the load with a new server that has just been brought online.

Connection Reset

If True, the connection state is reset when a pooled connection is reused. This requires an extra round-trip but makes for easier programming. State includes session-level SET statements and the currently selected database. This setting is recommended and is True by default.

Enlist

When True (the default), the connection is enlisted in the current transaction context of the creation thread.

Max Pool Size

The maximum number of connections allowed in the pool (100 by default). If the maximum pool size is reached and no connection is free, the request is queued and performance suffers.

Min Pool Size

The minimum number of connections always retained in the pool (0 by default). This number of connections is created when the first connection is created, leading to a minor delay for the first request.

Pooling

When True (the default), connection pooling is used. The SqlConnection object is drawn from the appropriate pool when needed or is created and added to the pool.

It's important to remember that most forms of connection pooling, including those used in the managed providers for SQL Server and Oracle, use the connection string to determine whether a connection can be reused. If there is any difference in the connection string used by different clients, the connections are created and maintained in separate pools. This is true whether the connection string specifies a different user for authentication and even if the connection string just has an extra space or changes the order of identical settings because connection pooling algorithms require a full-text match. This is an excellent reason to deny the client the chance to specify the connection string directly. Instead, this variable should be hard-coded in a centrally located configuration file, as demonstrated in Chapter 2, so that the component is guaranteed to use the same string for each client.

As a side effect, this also means that you can't viably enforce security at the database layer because every client would need to use a connection string that specifies a distinct user ID. The option for integrated security (SSPI) might appear to solve this problem; it is just as limited, however, because it always uses the security context of the process on the machine where it is running, not the security context of the requesting client. The only viable way to implement security is at the business level, through your code (typically, by using roles). Chapter 13 returns to this subject.

Connections and Application Domains

Connection pooling won't immediately benefit your client/server applications because it's tied to the application process. This means that if you have multiple independent clients using a local database component, they will each have their own local pool. Connections won't be shared between clients, and scalability will suffer (as shown in Figure 12-1).

Figure 12-1. Local connection pools

graphics/f12dp01.jpg

If, on the other hand, all these clients access the same database component through an XML Web service or .NET Remoting, the remote component can use connection pooling and a single pool can serve all clients (as shown in Figure 12-2).

Does this mean all your database components should be remote? Not necessarily remote components can be much slower because of the network latency and increased overhead in making a cross-process call. This is another example of the key tradeoff between scalability and performance examined in Chapter 10.

Figure 12-2. A shared connection pool

graphics/f12dp02.jpg

Testing Connection Pooling

To determine the ideal connection pooling settings, you might need to profile your application with performance counters. .NET includes performance counters specifically designed for the task under the .NET common language runtime (CLR) Data counter group. However, these counters work only with the SQL Server provider. You also might be able to use provider-specific counters. For example, SQL Server includes a counter that shows the current number of open connections.

One way to get a better understanding of how connection pooling works is to create a simple test application that opens and closes a connection to a database. You can use this application in conjunction with performance counters to monitor the effect of different connection string settings. This is the approach we'll take in the following example. It assumes you're using Microsoft Windows 2000 or Windows XP, but the process is similar in most Windows versions.

  1. Start Performance Monitor by choosing Programs | Administrative Tools | Performance from the Start menu.

  2. In the Performance window, click each existing counter and then click Delete to remove it. Continue this process until all the counter entries (in the lower right of the window) are removed.

  3. Right-click on the graph display and choose Add Counters. Select the SQLServer:GeneralStatistics category and the User Connections counter (as shown in Figure 12-3).

    Figure 12-3. The SQL Server connection counter

    graphics/f12dp03.jpg

Now create the simple XML Web service shown in Listing 12-1. Take note of the connection string it defines several important pooling settings, including the minimum number of pooled connections (5), the maximum simultaneous connections allowed (10), and the timeout before an error is delivered if a client attempts to create an 11th connection (5 seconds).

Listing 12-1 An XML Web service that tests connection pooling
 Imports System.Data.SqlClient Imports System.Web.Services Public Class ConnectionPoolTest     Inherits System.Web.Services.WebService     Private ConnectionString As String = "Data Source=localhost;" & _      user id=sa;Min Pool Size=5;Max Pool Size=10;Connect Timeout=5"     <WebMethod()> _     Public Sub TestConnect()         Dim con As New SqlConnection(ConnectionString)         con.Open() 
         con.Close()     End Sub End Class 

The TestConnect method simply creates a connection and releases it immediately. You can test this method by triggering it with the Microsoft Internet Explorer test page. The result is reflected in the performance counter, as shown in Figure 12-4.

Figure 12-4. Opening a single connection

graphics/f12dp04.jpg

Because the minimum pool size is set to 5, five connections are created the moment the first connection is requested. Invoking the TestConnect method more than once doesn't change the number of connections because the required connection is reused from the pool. All five connections are tied to the lifetime of the ASP.NET application. They live until you manually terminate the ASP.NET worker process (using Task Manager) or until you recompile the application, at which point your XML Web service is automatically migrated to a new application domain, which doesn't yet have a pool of existing connections.

Listing 12-2 shows another interesting test you might want to perform with the ConnectionPoolTest XML Web service. It opens two connections, one that uses a slightly different connection string (it has an extra space character added to the end). This minor difference is enough to trigger the creation of two pools. Therefore, when you invoke this method you'll end up with a total of 10 connections (as shown in Figure 12-5).

Listing 12-2 Creating two pools
 <WebMethod()> _ Public Sub TestTwoPools()     Dim conA As New SqlConnection(ConnectionString)     conA.Open()     conA.Close()     ' Modify the second connection string ever-so-slightly.     Dim conB As New SqlConnection(ConnectionString & " ")     conB.Open()     conB.Close() End Sub 
Figure 12-5. Opening two connections in separate pools

graphics/f12dp05.jpg

Listing 12-3 shows two new Web methods designed to test the effect of opening 10 connections, both consecutively and concurrently. When the connections are opened one after another (using the TestMultipleConsecutive method), there is no effect on the total number of connections. The pool of 5 connections remains, as shown in Figure 12-4. Each of the 10 new connections is drawn from this pool and returned in time for subsequent requests.

However, the story changes when the connections are opened at the same time (using the TestMultipleConcurrent method). Now 10 connections are required, and the pool is expanded to accommodate this need. This graph matches the display shown in Figure 12-5.

Listing 12-3 Testing consecutive and concurrent connections
 <WebMethod()> _ Public Sub TestMultipleConsecutive()     ' Open 10 connections, one after the other.     Dim i As Integer     For i = 1 To 10         Dim con As New SqlConnection(ConnectionString)         con.Open()         con.Close()     Next End Sub <WebMethod()> _ Public Sub TestMultipleConcurrent()     ' Open 10 connections at the same time.     Dim Connections As New ArrayList()     Dim i As Integer     For i = 1 To 10         Dim con As New SqlConnection(ConnectionString)         Connections.Add(con)         con.Open()     Next     ' Close all connections.     Dim OpenConnection As SqlConnection     For Each OpenConnection In Connections         OpenConnection.Close()     Next End Sub 

As a final test, consider what happens if you modify the TestMultipleConcurrent method so that it attempts to open 11 connections simultaneously. In this case, the 11th connection attempt is stalled (because there are no available connections and no new connections can be created without violating the maximum pool size setting). Now the code generates a timeout error after 5 seconds.

Experiment with these examples until you have a thorough understanding of how connection pooling works. You should understand that even when your code closes a connection, it actually remains open in the pool. However, it's imperative that you call Close as soon as possible because this makes the connection available to serve another client request.

Note

This example concentrated on an XML Web service application. Connection pooling works identically with .NET Remoting. The only difference is that the lifetime of the pool is tied to the lifetime of the component host, not the ASP.NET application.




Microsoft. NET Distributed Applications(c) Integrating XML Web Services and. NET Remoting
MicrosoftВ® .NET Distributed Applications: Integrating XML Web Services and .NET Remoting (Pro-Developer)
ISBN: 0735619336
EAN: 2147483647
Year: 2005
Pages: 174

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net