A connection in the .Net Teradata Provider For Teradata is managed by the TdConnection class. When TdConnection.Open is called, a connection is established to the Advanced SQL Engine specified in the connection string using the Data Source attribute. When a connection is established, a Teradata Session is opened and internal objects are initialized. Each connection manages one Teradata Session.

Each time an application calls TdConnection.Open to open a connection to a SQL Engine, the provider goes through the overhead of opening a Teradata Session and initializing internal objects associated with the connection. For applications that continually Open and Close connections, this overhead will significantly degrade performance. To reduce this overhead, the provider supports Connection Pooling.

Enabling Connection Pooling

Connection Pooling is enabled by setting the ConnectionPooling connection string attribute to true. By default Connection Pooling is enabled.

There are also several attributes that can be used to control a Connection Pool:

Attribute	Description	Default Value
Connection Pooling Timeout	The amount of time that a connection will remain idle in a Connection Pool.	60 seconds
Max Pool Size	The maximum number of connections allowed in a pool.	100
Min Pool Size	The minimum number of connections allowed in a pool.	0

The following is an example of a connection string that contains pooling attributes:

     Data Source=tdat1;User Id=user1;Password=pass1;Connection Pooling=true;MaxPoolSize=500;

How Connection Pooling Works

By using a Connection Pool, the number of times that a new session needs to be established to Teradata is greatly reduced. The pool maintains the ownership of all connections.

The Teradata provider can manage several Connection Pools. A pool is identified by the Connection String. Therefore, all connections in a pool will have the same connection string. When a different Connection String is specified, another pool will be created when TdConnection.Open is called. The connection that is returned will come from this pool.

Connection Strings can differ by the number of spaces used, order of attributes, or the addition/removal of attributes in the connection string. For example, the connections that are opened using the following Connection Strings are managed by different Connection Pools:

     Data Source=tdat1;User Id=user1;Password=pass1;

     Data Source=tdat1;       User Id=user1;Password=pass1;

     Data Source=tdat2;User Id=user1;Password=pass1;

     User Id=user1;Password=pass1;Data Source=tdat2;

     Data Source=tdat1;User Id=user1;Password=pass1;AccountName=acc1;

When an application calls TdConnection.Open, the following tasks are performed by the provider:

1. A lookup is performed to check whether any Connections Pools are associated with the Connection String. If a pool is not found, one is created.
2. Another lookup is performed to check whether any existing connections are available in the pool.
3. If a connection is available, it is returned to the application.
4. If no connections are available, a new connection to Teradata is established, and then returned to the application.
5. When TdConnection.Close is called, the connection is returned to the pool so that it can be reused.

Connection Pooling and Query Bands

When an application is using Query Bands, the Query Band values specified in the Connection String may need to be changed each time TdConnection.Open is called. This is especially true when Trusted Sessions are being used.

To prevent new connection pools from being created, the Query Band connection string attribute is ignored when the provider associates a Connection String to a pool. For example, the following connection strings are associated to the same pool:

     Data Source=tdat1;User Id=user1;Password=pass1;QueryBand='Application=app1';

     Data Source=tdat1;User Id=user1;Password=pass1;

     QueryBand='Application=app1';Data Source=tdat1;User Id=user1;Password=pass1;

     Data Source=tdat1;QueryBand='Application=app1;MaxQueryTime=10';User Id=user1;Password=pass1;

     Data Source=tdat1;   QueryBand='Action=insert'      ;User Id=user1;Password=pass1;