Bug #60366 Calling stored procedure with output parameters may hugely decrease performance
Submitted: 7 Mar 2011 10:43 Modified: 4 May 2011 14:25
Reporter: Pavel Bazanov Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / NET Severity:S2 (Serious)
Version:6.3.5, 6.3.6 OS:Windows
Assigned to: CPU Architecture:Any
Tags: OUT, Output, Parameters, params, performance, slow, SP, stored procedures

[7 Mar 2011 10:43] Pavel Bazanov
Description:
Calling stored procedures with output parameters decreases performance by up to 5+ times.
For example, if I have a stored procedure with 7 input parameters, then 10000 calls to ExecuteNonQuery() (if a server is local) takes 1500 ms. If I add 6 output paramaters, then 10000 calls to ExecuteNonQuery() will take 12000 ms!

The problem is in MySqlConnector, because:
1) If I call stored procedure with output parameters from a cycle inside another stored procedure then 100000 calls take only 1.5 sec.
2) If I use the same code and same procedure with DevArt's dotConnect (commercial ADO.NET provider for MySQL) then 10000 calls will take 3000 ms, not 12000 as with MySqlConnector.NET.

How to repeat:
CREATE DEFINER = 'root'@'localhost'
PROCEDURE rabase.TestProc(IN Param1 INT, IN Param2 VARCHAR(40), IN Param3 INT, IN Param4 INT, IN Param5 BOOLEAN, IN Param6 BOOLEAN, IN Param7 VARCHAR(10), OUT Param8 INT, OUT Param9 DECIMAL(13,3), OUT Param10 INT, OUT Param11 DECIMAL(13,3), OUT Param12 INT, OUT Param13 DECIMAL(13,3))
BEGIN
  SET Param2 = 'a';
END

			MySqlConnection conn = new MySqlConnection(
				"server=localhost;database=xxxxxx;User Id=root;password=yyyyyy;Character Set=cp1251");
			conn.Open();
			var command = conn.CreateCommand();
			command.CommandText = "TestProc";
			command.CommandType = CommandType.StoredProcedure;

			var p8 = new MySqlParameter("Param8", MySqlType.Int);
			p8.Direction = ParameterDirection.Output;
			var p9 = new MySqlParameter("Param9", MySqlType.Decimal);
			p9.Direction = ParameterDirection.Output;
			var p10 = new MySqlParameter("Param10", MySqlType.Int);
			p10.Direction = ParameterDirection.Output;
			var p11 = new MySqlParameter("Param11", MySqlType.Decimal);
			p11.Direction = ParameterDirection.Output;
			var p12 = new MySqlParameter("Param12", MySqlType.Int);
			p12.Direction = ParameterDirection.Output;
			var p13 = new MySqlParameter("Param13", MySqlType.Decimal);
			p13.Direction = ParameterDirection.Output;

			command.Parameters.AddWithValue("Param1", 1);
			command.Parameters.AddWithValue("Param2", "ABCDEFGHIJK");
			command.Parameters.AddWithValue("Param3", 2);
			command.Parameters.AddWithValue("Param4", 3);
			command.Parameters.AddWithValue("Param5", true);
			command.Parameters.AddWithValue("Param6", false);
			command.Parameters.AddWithValue("Param7", "");
			
			command.Parameters.Add(p8);
			command.Parameters.Add(p9);
			command.Parameters.Add(p10);
			command.Parameters.Add(p11);
			command.Parameters.Add(p12);
			command.Parameters.Add(p13);
			
			command.ExecuteNonQuery(); 
			
			Stopwatch sw = Stopwatch.StartNew();
			for (int j = 0; j < 10000; j++)
			{
				command.ExecuteNonQuery();
			}
			MessageBox.Show(sw.ElapsedMilliseconds.ToString());

			conn.Close();
[7 Mar 2011 10:44] Pavel Bazanov
fixed the category.
[7 Mar 2011 10:48] Pavel Bazanov
PS.
I took the code in the How to repeat section from the test with dotConnect. It uses MySqlType enumeration. For testing with MySqlConnector.NET you just need to change it to DbType enumeration.
[12 Mar 2011 9:06] Pavel Bazanov
Someone please take a look at this issue.
[17 Mar 2011 22:53] Pavel Bazanov
I think this is serious, I can't use stored procedures with output parameters in performance critical areas because of this problem :(
[8 Apr 2011 22:38] Pavel Bazanov
up
[4 May 2011 14:25] Reggie Burnett
Fixed in 6.3.7, 6.4.1+
[7 May 2011 16:24] Paul DuBois
Noted in 6.3.7, 6.4.1 changelogs.

Calling a stored procedure with output parameters caused a marked performance
decrease.

CHANGESET - http://lists.mysql.com/commits/136669