Why should I use connection pooling?

Question

In my C# application I connect to a MySQL database and run 10,000 queries. If I keep a connection to my database, these queries take roughly 14 seconds. However, if I rely on the connection pooling my queries take around 15 seconds. (I have run this test multiple times.)

// Connection pooling.
using (var connection = CreateConnection())
{
    connection.ConnectionString = ConnectionString;
    connection.Open();

Most samples on the net make use of the 'connect and close' construction above. However, it seems connection pooling is slower than keeping the connection. So the question is...

Q: Why should I use connection pooling?

Answer 1

Its a big debatable topic and would find many blog out there would tell that why we use Pool.

It will not slow things down. There is a lot of time spend on Connecting to DB Server and Hand shake and establishing communication between client and DB server.

So in multi request paradigm where many request are entertained by the server, it would be hard to establish and put on wait each client. POOL helps us that it gives us pre prepared connection and we use it and discard it. POOL get that connection and re-establish it for the next request.

But in a single threaded environment it is the other way around. POOL would be a very heavy resource for a single threaded env.

Answer 2

Q: Why should I use connection pooling?

Usually so that you can use more than one connection at a time. This is clearly important for web applications - you wouldn't want one user query to have to wait for another user's query to finish.

If you're writing a thick client application which talks straight to the database and you know you'll only ever have one query executing at a time, it's less important - but it's still global state, and that tends to be something you should avoid. You're doing several independent things - why would you want to constrain them to use the same connection?