CODEWITHSUNDEEP
c#mysqlasp.netado.net
fraserm
fraserm

Reputation: 3

ADO.NET takes >5 seconds to open MySql connection in ASP.NET form

Hoping you can help me out with some suggestions here.

I find that when I host a simple ASP.NET form on my web hosts servers (Vidahost), I get a 5 second delay when I call to Open() the connection.

The code I am using to narrow this issue down is below, and using asp.net tracing I see a consistent 5 second delay in between seeing the "Open Connection" and "New SQL Command" debug spew appear in the trace, implying that the .Open() call is taking way too long.

Vidahost have kindly done a Wireshark trace and report that: MySQL sends the Server Greeting. This gets acknowledged by the web server, taking around 5 ms The web server then seems to wait ~5 seconds before initiating the Login request

If I run exactly the same code on my laptop at home, using the external IP address to their MySql DB, the whole request completes in ~0.3s!

I am using the latest ADO.NET driver (v.6.9.9) which gets copied to my hosted website.

Can anyone help shed any possibilities on this? Is there any more advanced tracing we can do to dig into the connection being opened?

I`ve looked around for other issues of this nature, the main ones talk about the MySQL server reverse DNS lookup taking time to do, but I don't think this is relevant here - especially as it works perfectly running from my laptop at home.

The following code is in the Page_Load of the code behind.

try
{
    HttpContext.Current.Trace.Write("Setup connection with connection string");
    connMySql = new MySqlConnection(strConnectionString);

    HttpContext.Current.Trace.Write("Open Connection");
    connMySql.Open();

    HttpContext.Current.Trace.Write("New SQL Command");
    MySqlCommand cmdMySql = new MySqlCommand();
    cmdMySql.Connection = connMySql;

    HttpContext.Current.Trace.Write("Server: " + connMySql.DataSource.ToString());
    string sql = "";

    HttpContext.Current.Trace.Write("Setup SQL Query");
    sql = "SELECT * FROM tblret";
    cmdMySql.CommandText = sql;

    HttpContext.Current.Trace.Write("Call DB");

    MySqlDataReader dr = cmdMySql.ExecuteReader();

    HttpContext.Current.Trace.Write("DB Returned");

    int recordsReturned = 0;

    while (dr.Read())
        recordsReturned++;

    HttpContext.Current.Trace.Write("Parsed " + recordsReturned.ToString() + " records");
    textArea.Text = "Parsed " + recordsReturned.ToString() + " records";
}
catch (Exception ex)
{
    HttpContext.Current.Trace.Write("Error: " + ex.Message);            
}

Thanks, Fraser

Upvotes: 0

Views: 600

Answers (1)

Tim Schmelter
Tim Schmelter

Reputation: 460068

You should always dispose/close the connection as soon as you're finished with it. Therefore you can use the using-statement which ensures that it will be closed even in case of an error:

using(var connMySql = new MySqlConnection(strConnectionString))
{
    connMySql.Open();
    // ...
    // connMySql.Close(); not needed due to the using
}

I suspect that you are reusing the connection instance. Thats not a good idea since it prevents that ADO.NET can use connection-pooling. If it's even static all requests will share the same.

Upvotes: 1

Related Questions