Bug #72564 mysql connector .net performance issue
Submitted: 8 May 2014 6:22 Modified: 28 Jan 2015 5:35
Reporter: kevin wu Email Updates:
Status: Verified Impact on me:
None 
Category:Connector / NET Severity:S5 (Performance)
Version:6.7.4.0, 6.9.5 OS:Microsoft Windows (windows 7)
Assigned to: CPU Architecture:Any
Tags: mysql .net performance

[8 May 2014 6:22] kevin wu
Description:
MySql.Data.MySqlClient.MySqlParameterCollection.cs Line 99

If the count of parameters is very large, you may face a performance issue,it may take very long to add a parameter.

as of my test, with 70 thousands of parameters, it takes 40 seconds to process it, while System.Data.SqlClient.SqlParameterCollection only takes 20 milliseconds.
    

How to repeat:
//Create a command first, it may take up to 40 seconds to finish this
foreach (var p in Enumerable.Range(0, 70000))
{
    var param = command.CreateParameter();
    param.ParameterName = p.ToString();
    param.DbType = DbType.String;
    param.Size = 100;
    param.Value = p;
    param.Direction = ParameterDirection.Input;
    param.IsNullable = false;

    
    command.Parameters.Add(param);
}

Suggested fix:
//better not to use IndexOf method, use items.Count -1 instead

public override int Add(object value)
{
    MySqlParameter parameter = value as MySqlParameter;
    if (parameter == null)
        throw new MySqlException("Only MySqlParameter objects may be stored");

    parameter = Add(parameter);

    return items.Count - 1;
    //return IndexOf(parameter);
}
[28 Jan 2015 5:35] Chiranjeevi Battula
Hello kevin wu

Thank you for the feedback.
Verifying based on internal discussion with dev's.

Thanks,
Chiranjeevi.
[11 Sep 2015 11:38] Mikael Rydström
Visual Studio profiling session showing relationship between command parameter handling and actual query execution

Attachment: mysql_bulk_insert_profiling_2015-09-11.png (image/png, text), 40.56 KiB.

[11 Sep 2015 11:38] Mikael Rydström
I can verify this is a big problem. In fact in my bulk insert scenarios, adding the command parameters takes nearly as much time as executing the actual command! Tested with 120k records, a few parameter values each record.

I estimate that my bulk inserts could be at least 30-35% faster if this (over one year old) bug was fixed. That's a big deal.

I am attaching some Visual Studio profiler results from my bulk routine:
- mysql_bulk_insert_profiling_2015-09-11.png

Note that the InternalAdd() and AddWithValue() calls together take longer to run than the actual query.

I agree with Kevin's analysis on the problematic code.