Taking Advantage of Connection Pooling

[ Team LiB ] Recipe 1.15 Taking Advantage of Connection Pooling Problem You need to understand connecting pooling and make sure that your applications use it. Solution To effectively use connection pooling, you need to understand the concepts underlying connection pooling
Nội dung trích xuất từ tài liệu:
Taking Advantage of Connection Pooling[ Team LiB ]Recipe 1.15 Taking Advantage of Connection PoolingProblemYou need to understand connecting pooling and make sure that your applications use it.SolutionTo effectively use connection pooling, you need to understand the concepts underlyingconnection pooling, how connection pooling is implemented by the major .NET dataproviders, and how to ensure that connection pooling is used by an application.DiscussionConnection pooling allows an application to reuse connections from a pool instead ofrepeatedly creating and destroying new connections. Connection pooling cansignificantly improve the performance and scalability of applications by allowing asmaller number of connections to service the connection requirements of an applicationand because the overhead of establishing a new connection is eliminated.A connection pool is created for each unique connection string. An algorithm associatesitems in the pool based on an exact match with the connection string; this includescapitalization, order of name value pairs, and even spaces between name/value pairs.Dynamically generated connection strings must be identical so that connection pooling isused. If delegation is used, there will be one pool per delegate user. When transactionsare used, one pool is created per transaction context. (For more information, see Recipe1.17.) When the connection pool is created, connection objects are created and added tothe pool to satisfy the minimum pool size specified.When a connection is requested by an application and the maximum pool size has beenreached, the request is queued. The request is satisfied by reallocating a connection that isreleased back to the pool when the Connection is closed or disposed. The connection poolmanager removes expired connections and connections that have had their connectionwith the server severed from the pool.The Connection object should be closed as soon as it is no longer needed so that it isadded to or returned to the connection pool. This is done by calling either the Close( ) orDispose( ) method of the Connection. Connections that are not explicitly closed might notbe added to or returned to the connection pool. The DataAdapter automatically opens and closes a Connection as required if it is not already open when a method such as Fill( ), FillSchema( ), or Update( ) is called. The Connection must be explicitly closed if it is already open prior to the DataAdapter operation.The following subsections detail connection pooling for specific .NET Framework dataproviders.SQL Server and OracleThe .NET data providers for SQL Server and Oracle provide efficient, transaction-awaresupport for connection pooling. Pools are created for each process and not destroyed untilthe process ends. Connection pooling is enabled by default.Controlling SQL Server and Oracle .NET data provider connection pooling withconnection string attribute/value pairs is discussed in Recipe 1.16.OLE DBThe OLE DB .NET data provider pools connections by using resource pooling providedby the OLE DB core components.The default OLE DB services that are enabled for a provider are specified by the valuefor the registry HKEY_CLASSES_ROOTCLSIDOLE DB Services attribute in the connection string. Table 1-5 describes possible values. Table 1-5. OLE DB services connection string values OLE DB Services attribute Default services enabled value-1 All services (default)-2 All services except Pooling and AutoEnlistment-5 All services except Client Cursor All services except Pooling, AutoEnlistment, and-7 Client Cursor0 No servicesThe following three configurable settings control OLE DB connection pooling:SPTimeout The length of time in seconds that an unused connection remains in the pool before it is released. This can be configured for each provider and defaults to 60 seconds.Retry Wait The length of time in seconds before an attempt to acquire a connection is reattempted when the server is not responding. This is global to all providers and defaults to 64 seconds.ExpBackOff The factor by which the retry wait time is increased when a connection attempt fails before reattempting the connection. This is global to all providers and defaults to a factor of 2.OLE DB connection pooling is enabled by default; you can control it in three differentways: • Specify a value for the OLE DB Services attribute in the connection string. • Edit the ...