JDBC connection pools

A pooling data source uses a collection of persistent connections in order to reduce the overhead of repeatedly opening network connections between the client and server. Opening a new connection for each request is more costly for both the server and the client than keeping a small pool of connections open constantly, ready to be used by new requests. When a request comes in, one of the pre-existing connections in the pool is assigned to it. Only if there are no free connections in the pool is a new connection created. Once the request is complete, the connection returns to the pool and waits to service another request.

The OpenText™ Analytics Database JDBC driver supports connection pooling as defined in the JDBC 4.0 standard. If you are using a J2EE-based application server with the database, it should already have a built-in data pooling feature. All that is required is that the application server work with the PooledConnection interface implemented by the database's JDBC driver. An application server's pooling feature is usually well-tuned for the works loads that the server is designed to handle. See your application server's documentation for details on how to work with pooled connections. Normally, using pooled connections should be transparent in your code—you will just open connections and the application server will worry about the details of pooling them.

If you are not using an application server, or your application server does not offer connection pooling that is compatible with the database, you can use a third-party pooling library, such as the open-source c3p0 or DBCP libraries, to implement connection pooling.

Pooled Connections (Pooling=true)

The connection returns to the pool for reuse and is not physically closed. It is available for future use by other parts of the application. No new connection is created; the connection is reused, improving performance by avoiding the need to establish new connections.

Non-Pooled Connections (Pooling=false)

The connection is physically closed terminating the connection to the database. Whenever the Open() function is called, a new connection must be established. This impacts performance as the application needs to create and close the connections repeatedly.

The key difference is that the Close() function:

• returns the connection to the pool for pooled connections.

• closes the connection to the database for non-pooled connections.

  Example

  using System;
  using Vertica.Data.VerticaClient; // Vertica ADO.NET driver
  public class VerticaConnectionPoolExample
  
  {
      private static string connectionString = "Host=your-vertica-server;Database=your-database;
      User ID=your-user-id;Password=your-password;Pooling=true;Max Pool Size=100;Min Pool Size=5;";
  
          public static void Main(string[] args)
          {
              // Open and close some pooled connections
              for (int i = 0; i < 5; i++)
              {
                using (var connection = new VerticaConnection(connectionString))
                {
                  connection.Open();
                  Console.WriteLine($"Connection {i + 1} opened.");
                  // Simulate some database operations
                } 
                Console.WriteLine($"Connection {i + 1} closed and returned to pool.");
              }
              // Now we explicitly clear the connection pool
              // VerticaConnection.ClearPool() can be used to clear the pool of connections to the server
              // This is usually only necessary when you want to force all connections to be released
  
              VerticaConnection.ClearPool(new VerticaConnection(connectionString)); // Clear the connection pool
  
              Console.WriteLine("Vertica connection pool cleared.");
  
              // Exit the application
              Console.WriteLine("Application is exiting.");
          }
  }