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.
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 DomainsConnection 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
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
Testing Connection PoolingTo 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.
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 poolingImports 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
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
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. |