C# – how database connection pool impacts performance

ado.netasp.netcnetsql-server-2005

I am using .Net 2.0 + SQL Server 2005 Enterprise + VSTS 2008 + C# + ADO.Net to develop ASP.Net Web application. The ASP.Net Web application is database centric/driven. I want to know whether there are any performance reference data about what are the differences of performance when we turn on/off thread pool setting in ADO.Net connecting string setting? Performance I mean both the concurrent connection which could be supported and the execution time of a specific SQL Command executed from ADO.Net client side?

thanks in avdance,
George

Best Answer

I don't have any performance stats at hand, but maybe a word of warning when using connection pooling: you might end up with too many small pools, instead of one large one.

ADO.NET will create a new connection pool for

each connection string; it is very picky about this, too - if you have two connection strings that are different by even just a single space or something, those are considered two separate connection strings, and will lead to separate connection pools being created
for each Windows credentials if using the "integrated security" (trusted connection) setting

So if you have a connection string something like

server=MyDBServer;database=MyDatabase;integrated security=SSPI;

one connection pool for each distinguishable user will be created - that's rather counter-intuitive, but that's the way it is (and it cannot be influenced / turned off).

Check out the MSDN docs on ADO.NET connection pooling for details:

When a connection is first opened, a connection pool is created based on an exact matching algorithm that associates the pool with the connection string in the connection. Each connection pool is associated with a distinct connection string. When a new connection is opened, if the connection string is not an exact match to an existing pool, a new pool is created. Connections are pooled per process, per application domain, per connection string and when integrated security is used, per Windows identity. Connection strings must also be an exact match; keywords supplied in a different order for the same connection will be pooled separately.

Also, if you have these two connection strings:

server=MyDBServer;database=MyDatabase;user id=tom;pwd=top$secret

and

server=MyDBServer;database=MyDatabase;user id=tom; pwd=top$secret;

those are considered different connection strings, and thus two separate connection pools will be created.

When attempting to measure the effect of connection pooling, this is something to be aware of!

Marc

Related Solutions

C# – How to calculate someone’s age based on a DateTime type birthday

An easy to understand and simple solution.

// Save today's date.
var today = DateTime.Today;

// Calculate the age.
var age = today.Year - birthdate.Year;

// Go back to the year in which the person was born in case of a leap year
if (birthdate.Date > today.AddYears(-age)) age--;

However, this assumes you are looking for the western idea of the age and not using East Asian reckoning.

C# – How to cast int to enum

From an int:

YourEnum foo = (YourEnum)yourInt;

From a string:

YourEnum foo = (YourEnum) Enum.Parse(typeof(YourEnum), yourString);

// The foo.ToString().Contains(",") check is necessary for enumerations marked with an [Flags] attribute
if (!Enum.IsDefined(typeof(YourEnum), foo) && !foo.ToString().Contains(","))
{
    throw new InvalidOperationException($"{yourString} is not an underlying value of the YourEnum enumeration.")
}

Update:

From number you can also

YourEnum foo = (YourEnum)Enum.ToObject(typeof(YourEnum) , yourInt);

Best Answer

Related Solutions

C# – How to calculate someone’s age based on a DateTime type birthday

C# – How to cast int to enum

Related Topic