MySQL Bugs: #72564: mysql connector .net performance issue

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

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);
}

Hello kevin wu

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

Thanks,
Chiranjeevi.

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.

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.

Posted by developer:
 
This is no longer a bug. Verified using latest version of Connector/NET, v8.0.32.