10

I have TcpListener class and I'm using async/await reading and writing.

For this server I have created single database instance where I have prepared all database queries.

But for more then one TcpClient I'm keep getting exception:

An exception of type MySql.Data.MySqlClient.MySqlException occurred in MySql.Data.dll but was not handled in user code

Additional information: There is already an open DataReader associated with this Connection which must be closed first.

If I understand it correctly there can't be more then one database query at time which is problem with more then one async client.

So I simply added locks in my queries like this and everything seems fine.

   // One MySqlConnection instance for whole program.

   lock (thisLock)
   {
    var cmd = connection.CreateCommand();

    cmd.CommandText = "SELECT Count(*) FROM logins WHERE username = @user AND password = @pass";
    cmd.Parameters.AddWithValue("@user", username);
    cmd.Parameters.AddWithValue("@pass", password);

    var count = int.Parse(cmd.ExecuteScalar().ToString());
    return count > 0;
}

I have also try the method with usings which create new connection for every query as mentioned from someone of SO community but this method is much more slower than locks:

    using (MySqlConnection connection = new MySqlConnection(connectionString))
    {
        connection.Open();   // This takes +- 35ms and makes worse performance than locks

        using (MySqlCommand cmd = connection.CreateCommand())
        {
            cmd.CommandText = "SELECT Count(*) FROM logins WHERE username = @user AND password = @pass";
            cmd.Parameters.AddWithValue("@user", username);
            cmd.Parameters.AddWithValue("@pass", password);

            int count = int.Parse(cmd.ExecuteScalar().ToString());
            return count > 0;
        }
    }

I used Stopwatch to benchmarks this methods and queries with one connection with locks are performed in +- 20ms which is +- only delay of network but with usings it is +- 55ms because of .Open() method which takes +- 35ms.

Why a lot of people use method with usings if there is much worse performance? Or am I doing something wrong?

9
  • Have you enabled MARS and set asynchronous processing on your connection string? Commented Apr 26, 2017 at 13:06
  • @Paulo Morgado I have MySQL not SQL. MySQL does not have MARS as i found on google. Commented Apr 26, 2017 at 13:24
  • 1
    Instead of the lock why not open another connection? Commented Apr 26, 2017 at 14:45
  • @JSteward I have made some tests and opening new connection will take much more time. Commented Apr 27, 2017 at 8:36
  • Isn't using just syntactic sugar? Commented May 2, 2017 at 17:07

1 Answer 1

8
+50

You're right, opening connection is a time-consuming operation. To mitigate this, ADO.NET has Connection pooling. Check this article for details.

If you go on with your performance test and check timings for subsequent connections, you should see that time for connection.Open() improves and gets close to 0 ms because connections are actually taken from the Pool.

With your lock implementation, you actually use connection pool with just one connection. While this approach could show better performance within a trivial test, it will show very poor results in highly loaded applications.

Sign up to request clarification or add additional context in comments.

4 Comments

i will try to test it.
In addition to this it's probably worth adding a mention of using an ORM where this type of work is already built for you (connection pool handling within context instances).
I agree, a single lock in a highly concurrent application will show terrible performance compared to using ADO.NET connection pooling.
I can confirm this now. Thank you for got things clear to me.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.