Connection pooling not working
Posted by: Fabricio Rodriguez
Date: August 12, 2014 01:55AM

I have a Windows Forms app targeting .NET framework 4.0, which connects to a MySQL Server 5.5.9 database running on Windows Web Server 2008 R2. I'm using the MySQL .NET Connector 6.8.3 for the data access. The database is hosted at my ISP on a dedicated server.

I don't think "connection pooling" is working properly, or I'm doing something wrong. Here's why I think this is the case:

I recently read that it is good practise to use the MySqlHelper classes instead of opening/closing connections oneself. One of the benefits being that it uses connection pooling. So I performed a little expirement.

I executed a query 100 times in a loop. I did this twice. Firstly, using the helper classes:

For i As Integer = 1 To 100
MySqlHelper.ExecuteNonQuery(connectionString, "DELETE FROM users")
Next i

I then tried it the way I normally do it:

Using cn As New MySqlConnection(connectionString)
cn.Open()
Dim cmd As New MySqlCommand("DELETE FROM users", cn)
For i As Integer = 1 To 100
cmd.ExecuteNonQuery()
Next i
cn.Close()
End Using

The users table was empty to begin with.

The first loop took an average of 23 seconds to execute over 5 runs.
The second loop took an average of 7 seconds to execute over 5 runs.

I can only attribute this to the fact that the first loop is opening and closing the connection 100 times, whereas the second loop is opening and closing the connection only once. But, here comes my query... isn't connection pooling supposed to keep the connection open behind the scenes (I think for 3 minutes by default I read somewhere?), so that the two loops should run at very similar speeds? It feels like connection pooling is not working, or active, or I'm doing something wrong... Any ideas anyone?

Thanks...

Options: ReplyQuote


Subject
Written By
Posted
Connection pooling not working
August 12, 2014 01:55AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.