Bug #61647 Executing stored procedures with input parameters performance decreased by 20%
Submitted: 26 Jun 2011 19:38 Modified: 4 Jul 2011 19:00
Reporter: Pavel Bazanov Email Updates:
Status: Not a Bug Impact on me:
None 
Category:Connector / NET Severity:S3 (Non-critical)
Version:6.3.7 OS:Any
Assigned to: Bogdan Degtyariov CPU Architecture:Any
Tags: IN, input, Parameters, params, performance, slow, SP, stored procedures

[26 Jun 2011 19:38] Pavel Bazanov
Description:
Hello, in version 6.3.7 you fixed the problem with slow execution of stored procedures with output parameters (bug #60366), but now stored procedures with input parameters work slower by ~20% (comparing to version 6.3.6).

The code from the How to repeat section shows ~1690 ms on version 6.3.6 and ~2050 ms on version 6.3.7.

How to repeat:
CREATE DEFINER = 'root'@'localhost' PROCEDURE `TestProc`(
        IN param1 INTEGER(11),
        IN param2 VARCHAR(250),
        IN param3 INTEGER(11),
        IN param4 INTEGER(11)
    )
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
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;

			command.Parameters.AddWithValue("Param1", 1);
			command.Parameters.AddWithValue("Param2", "ABCDEFGHIJK");
			command.Parameters.AddWithValue("Param3", 2);
			command.Parameters.AddWithValue("Param4", 3);
		
			command.ExecuteNonQuery(); 
			
			Stopwatch sw = Stopwatch.StartNew();
			for (int j = 0; j < 10000; j++)
			{
				command.ExecuteNonQuery();
			}
			MessageBox.Show(sw.ElapsedMilliseconds.ToString());

			conn.Close();
[4 Jul 2011 10:10] Bogdan Degtyariov
Pavel,

Your test is not entirely correct.
1.4s and 1.6s is still within the experiment error.
Try increasing the number of iterations from 10K to 100K and you will see the results are pretty even.

I measured the performance for versions 6.3.6 and 6.3.7 and the result intervals were overlapping:

6.3.6: 12652ms..12754ms 
6.3.7: 12715ms..12760ms
[4 Jul 2011 19:00] Pavel Bazanov
Why is my test is not entirely correct?

I get similar results with 100000 iterations:

6.3.6:
11345 ... 11600

6.3.7:
12346 ... 12826