Description:
Connector/NET goes with a SQL below when running procedure in C# code.
SELECT *
FROM mysql.proc
WHERE 1 = 1
AND db LIKE 'xxxx'
AND NAME LIKE 'xxxxx'
This SQL uses range scanning and most of rows in mysql.proc are scanned. And this caused performance issue in our environment: Above query took 94.01% of total execution time while number of executions were only 2.53% among total.
How to repeat:
Here is C# snippt where calling procedure:
MySqlConnection conn = new MySqlConnection();
conn.ConnectionString = "server=localhost;user=root;database=test;port=3306;password=******";
MySqlCommand cmd = new MySqlCommand();
conn.Open();
cmd.Connection = conn;
cmd.CommandText = "test_proc";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@param_1", "test");
cmd.Parameters["@param_1"].Direction = ParameterDirection.Input;
cmd.ExecuteNonQuery();
Suggested fix:
Is it possible to use SQL below instead in Connector/NET?
SELECT *
FROM mysql.proc
WHERE 1 = 1
AND db = 'xxxx'
AND NAME = 'xxxxx'
This SQL uses ref scanning and the only matched row is scanned. In my test, it's 10 times faster than using 'Like'.
Description: Connector/NET goes with a SQL below when running procedure in C# code. SELECT * FROM mysql.proc WHERE 1 = 1 AND db LIKE 'xxxx' AND NAME LIKE 'xxxxx' This SQL uses range scanning and most of rows in mysql.proc are scanned. And this caused performance issue in our environment: Above query took 94.01% of total execution time while number of executions were only 2.53% among total. How to repeat: Here is C# snippt where calling procedure: MySqlConnection conn = new MySqlConnection(); conn.ConnectionString = "server=localhost;user=root;database=test;port=3306;password=******"; MySqlCommand cmd = new MySqlCommand(); conn.Open(); cmd.Connection = conn; cmd.CommandText = "test_proc"; cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@param_1", "test"); cmd.Parameters["@param_1"].Direction = ParameterDirection.Input; cmd.ExecuteNonQuery(); Suggested fix: Is it possible to use SQL below instead in Connector/NET? SELECT * FROM mysql.proc WHERE 1 = 1 AND db = 'xxxx' AND NAME = 'xxxxx' This SQL uses ref scanning and the only matched row is scanned. In my test, it's 10 times faster than using 'Like'.