| 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: | |
| 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 | ||
[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.
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); }