Connection Pooling

In This Topic

The ADO.NET provider for Snowflake implements connection pooling to reduce the efforts of repeatedly opening and closing connections. A connection pool is a cache of database connections maintained where the user can reuse existing active connections with the same connection string instead of creating new connections when a request is made to the database.

Connection pools are used to enhance the performance of executing commands on a database. The provider supports pooling, by default with the pool size set to 100. However, pooling can be disabled by setting the UsePool property to false.

The following code demonstrates the implementation of connection pooling in DataConnector for Snowflake.

C#	Copy Code
//With Connection Pooling: It saves time by reusing existing connection objects from the pool Console.WriteLine("Establishing connection setting UsePool to True"); for (int i = 0; i < 1000; i++) { C1SnowflakeConnection conn = new C1SnowflakeConnection($@"{connectionString};Use Pool=true;"); conn.Open();//Soft connections are build //Perform Your Operation conn.Close();//Close the connection (returns the connection object to the connection pool) } Console.WriteLine("Connections established and closed !!! ");

Copy Code

//With Connection Pooling: It saves time by reusing existing connection objects from the pool
Console.WriteLine("Establishing connection setting UsePool to True");
for (int i = 0; i < 1000; i++)
{
    C1SnowflakeConnection conn = new C1SnowflakeConnection($@"{connectionString};Use Pool=true;");
    conn.Open();//Soft connections are build
    //Perform Your Operation 
    conn.Close();//Close the connection (returns the connection object to the connection pool)
}
Console.WriteLine("Connections established and closed !!! ");

To disable pooling, set the Use Pool key to false in the connection string as shown in the following code.

C#	Copy Code
//Without Connection Pooling Console.WriteLine("Establishing connection setting UsePool to false"); for (int i = 0; i < 1000; i++) { C1SnowflakeConnection conn = new C1SnowflakeConnection($@"{connectionString};Use Pool=false;"); conn.Open();//A new connection is created each time //Perform Your Operation conn.Close();//Close the connection (Connection object moved to garbage collection) } Console.WriteLine("Connections established and closed !!! ");

Copy Code

//Without Connection Pooling
Console.WriteLine("Establishing connection setting UsePool to false");
for (int i = 0; i < 1000; i++)
{
    C1SnowflakeConnection conn = new C1SnowflakeConnection($@"{connectionString};Use Pool=false;");
    conn.Open();//A new connection is created each time
    //Perform Your Operation 
    conn.Close();//Close the connection (Connection object moved to garbage collection)
}
Console.WriteLine("Connections established and closed !!! ");