Bug #105965 performance issue when adding parameters to the para collection

• Submitted: 23 Dec 2021 2:58
• Modified: 24 Dec 2021 13:52
• Reporter: Zheyuan Lin
• Status: Won't fix
• Category: Connector / NET
• Severity: S5 (Performance)
• Version: 8.0.27
• OS: Any
• CPU Architecture: Any

[23 Dec 2021 2:58] Zheyuan Lin

Description:
When I add large number of parameters （more than 5000） to a MySqlParameterCollection using MySql.Data.MySqlClient.MySqlParameterCollection.Add(object value) method, it will take a long time to finish it (with high cpu usage). 
Use AddWithValue(string parameterName, object value) method is ok.
I use Dapper, so I can't resolve this problem.

How to repeat:
Add more than 5000 difference parameters to a MySqlParameterCollection with "MySql.Data.MySqlClient.MySqlParameterCollection.Add(object value)".

Suggested fix:
I noticed that MySql.Data.MySqlClient.MySqlParameterCollection.Add(object value) using "return this.IndexOf(mySqlParameter);" 
that causes performance issue when there are large number of parameters in the collection. 
Maybe it's better to use "this.IndexOf(string parameterName)" instead?

[23 Dec 2021 12:33] MySQL Verification Team

Hello Zheyuan Lin,

Thank you for the bug report.
May I request you to please provide a simple test case(c# class) to reproduce this issue at our end? If you can provide more information, feel free to add it to this bug and change the status back to 'Open'.  

Regards,
Ashwini Patil

[23 Dec 2021 15:25] Zheyuan Lin

Test Code:

int paramCount = 50000;
var cmd = new MySqlCommand();
var sw1 = new Stopwatch();
sw1.Start();
for (int i = 0; i < paramCount; i++)
{
	IDbDataParameter p = cmd.CreateParameter();
	p.ParameterName = $"?param_{i}";
	p.DbType = DbType.String;
	cmd.Parameters.Add(p);
}
Console.WriteLine(sw1.Elapsed);
var cmd2 = new MySqlCommand();
var sw2 = new Stopwatch();
sw2.Start();
for (int i = 0; i < paramCount; i++)
{
	IDbDataParameter p = cmd2.CreateParameter();
	p.ParameterName = $"?param_{i}";
	p.DbType = DbType.String;
	cmd2.Parameters.AddWithValue(p.ParameterName, p);
}
Console.WriteLine(sw2.Elapsed);

[23 Dec 2021 15:38] Zheyuan Lin

Use "Add(object)" And "Add(MySqlParameter)" make big different.

int paramCount = 50000;
var cmd = new MySqlCommand();
var sw1 = new Stopwatch();
sw1.Start();
for (int i = 0; i < paramCount; i++)
{
	IDbDataParameter p = cmd.CreateParameter();
	p.ParameterName = $"?param_{i}";
	p.DbType = DbType.String;
	cmd.Parameters.Add(p);
}
Console.WriteLine(sw1.Elapsed);
var cmd2 = new MySqlCommand();
var sw2 = new Stopwatch();
sw2.Start();
for (int i = 0; i < paramCount; i++)
{
	MySqlParameter p = cmd2.CreateParameter();
	p.ParameterName = $"?param_{i}";
	p.DbType = DbType.String;
	cmd2.Parameters.Add(p);
}
Console.WriteLine(sw2.Elapsed);

Just a example. I think it can help you find the issue.

[24 Dec 2021 13:21] MySQL Verification Team

Hello Zheyuan Lin,

Thank you for the requested details.
Please note that MySqlParameterCollection.Add(String, Object) is obsolete since C/NET 8.0.14 and it is recommended and suggested to use MySqlParameterCollection.AddWithValue instead.
For more info, please see https://dev.mysql.com/doc/relnotes/connector-net/en/news-8-0-14.html

Regards,
Ashwini Patil

[24 Dec 2021 13:52] Zheyuan Lin

Please note that the issue cause by "Add( Object):int ",NOT "Add(String, Object):MySqlParameter " . 
THEY ARE DIFFERENT.
THE VERSION IS "8.0.27", you can't find a method "MySqlParameterCollection.Add(String, Object) " IN this Version becasue it's obsolete.
"Add( Object) :int" is inherited from  DbParameterCollection.

[24 Dec 2021 18:51] Bradley Grainger

This seems like an incomplete fix for bug #62653: the Add(object) overload still calls IndexOf, which results in quadratic behaviour: https://github.com/mysql/mysql-connector-net/blob/4306c8484ec74b3ee1c349847f66acabbce6d63c...

The fix would be the same: remove the IndexOf call and return _items.Count - 1.

[29 Dec 2021 5:04] MySQL Verification Team

Thank you  Bradley, for the feedback.
Bug #105997 has been handled now. 

regards,
Umesh