mysql-connector-net-6.9.12 Performance Issue when Calling Procedure
Posted by: Bill Guo
Date: May 13, 2019 02:56AM

I found mysql connetor/NET using a inefficient way when running procedures.

1. Reproduce:
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();


2. Problem:
Connector/NET went with a SQL below
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.


3. Suggestion:

Can we just use SQL below instead?
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'.

Options: ReplyQuote


Subject
Written By
Posted
mysql-connector-net-6.9.12 Performance Issue when Calling Procedure
May 13, 2019 02:56AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.