Bug #62653 Poor performance when adding parameters to MysqlCommand
Submitted: 7 Oct 2011 16:13 Modified: 31 Oct 2011 17:36
Reporter: Marc Sàbat Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / NET Severity:S5 (Performance)
Version:6.4.4 OS:Any
Assigned to: Reggie Burnett CPU Architecture:Any
Tags: AddWithValue, internaladd, Parameter, Parameters, performance

[7 Oct 2011 16:13] Marc Sàbat
Description:
To add parameters to a MysqlCommand the function AddWithValue is often used. Doing so calls the function InteralAdd (file parameter_collection.cs).
This function returns the index of the inserted parameter. To find the index, though, uses the function IndexOf of a generic list. This is not optimal, especially for large lists of parameters.

Note that using Insert instead of AddWithValue to add parameters is even worse: it triggers the function AdjustHashes and its terribly slow for high number of parameters.

By the way, to find the culprit of al this I used the excellent SlimTune Profiler (http://code.google.com/p/slimtune/).

How to repeat:
Just use AddWithValue with a large number of parameters and compare with the suggested fix.
For "high number of parameters" in my case I used a Insert Into with multiple lines in one (1000 lines in my case). Example:

Insert Into(a,b,c) values(?1,?2,?3), (?4,?5,?6), etc.

Suggested fix:
If I am not wrong, when adding a new element to a generic list it will be the last one. So, at the file parameter_collection.cs, at line 446 should be replaced by this:

if (index == -1)
{
  items.Add(value);
  //index = items.IndexOf(value);  <- This is how it's currently done
  index = items.Count - 1; <-I think this should be correct and without performance penalty
}

BTW: Without this fix the time spent just adding parameters was about 20% of total time. With this fix adding parameters was just 1% of total time (and the process went from 3 to 2 min).
[11 Oct 2011 16:25] Marc Sàbat
Microsoft's documentation confirms that adding an element to a generic list does so at the end:
http://msdn.microsoft.com/en-us/library/3wcytfd1.aspx
[31 Oct 2011 17:36] Reggie Burnett
Fixed in 6.3.8, 6.4.5, and 6.5+
[23 Nov 2011 1:20] Philip Olson
Fixed as of Connector/NET 6.3.8 and 6.4.5:
+        Connector/NET experienced poor performance when adding
+        parameters to the <literal>MySQLCommand</literal>.