Mysql query process check via Wireshark

Question

I am trying to understand what's happening in the background, when a simple select query executed by client.

I am using C# Asp.Net Webforms, and i checked the processes with WireShark.

public DBC(string procedureName, params object[] procParams)
{
    strError = null;
    using (MySqlConnection connection = new MySqlConnection(GetConnectionString()))
    {
        connection.Close();
        try
        {
            connection.Open();
            MySqlCommand cmd = new MySqlCommand(procedureName, connection);
            cmd.CommandType = CommandType.StoredProcedure;

            //if we use params for stored procedure
            if (procParams != null)
            {
                int i = 1;
                foreach (object paramValue in procParams)
                {
                    cmd.Parameters.Add(new MySqlParameter("@param_" + i, paramValue.ToString()));
                    i++;
                }
            }

            if (procedureName.Contains("get"))
            {
                dtLoaded = new DataTable();
                dtLoaded.Load(cmd.ExecuteReader());
            }
            else
            {
                cmd.ExecuteNonQuery();
            }
        }
        catch (Exception ex)
        {
            strError = ErrorHandler.ErrorToMessage(ex);
        }
        finally
        {
            connection.Close();
            connection.Dispose();
        }
    }
}

This is a simple SELECT * FROM TABLE query, in a try-catch statement. At the finally state, the connection was closed and disposed.

Why is it causes 43 process? I don't understand, why is there so much. Somebody could explain me?

Many thanks!

Answer 1

I assume you're using Oracle's Connector/NET. It performs a lot of not-strictly-necessary queries after opening a connection, e.g., SHOW VARIABLES to retrieve some server settings. (In 8.0.17 and later, this has been optimised slightly.)

Executing a stored procedure requires retrieving information about the stored procedure (to align parameters); it's more "expensive" than just executing a SQL statement directly. (You can disable this with CheckParameters=false, but I wouldn't recommend it.)

You can switch to MySqlConnector if you want a more efficient .NET client library. It's been tuned for performance (in both client CPU time and network I/O) and won't perform as much unnecessary work when opening a connection and executing a query. (MySqlConnector is the client library used for the .NET/MySQL benchmarks in the TechEmpower Framework Benchmarks.)