Bug #72728 C/NET slow querying procedures
Submitted: 23 May 2014 8:30 Modified: 15 Apr 2023 0:06
Reporter: Andrew Dalgleish Email Updates:
Status: Won't fix Impact on me:
Category:Connector / NET Severity:S3 (Non-critical)
Version:6.8.3 OS:Any
Assigned to: CPU Architecture:Any

[23 May 2014 8:30] Andrew Dalgleish
Using the following code with C/NET
MySqlCommand cmd = new MySqlCommand("proc_2", conn);
cmd.CommandType = CommandType.StoredProcedure;

Generates a query like this:

If you have a large number of procedures this can be very slow.

Querying the I_S.ROUTINES is much slower than querying mysql.proc table, 2x- 5x slower on my test VM.

Using LIKE (instead of an exact match) return a "same name are not supported" error if the procedure name includes a wildcard character and matches more than one procedure.

How to repeat:
Use C/NET to call a stored procedure, and check the general log.

Suggested fix:
Query the mysql.proc table instead of I_S.ROUTINES, and use an exact match instead of LIKE.
[15 May 2015 5:47] Tushar Agarwal
I am using 6.9.6. 
In my connection string I have added 
Use Procedure Bodies=true;ProcedureCacheSize=2500 ,
given all permissions on mysql.proc to my user but it doesn't work. I_S is being queried.

Is there something that I am missing ?
[15 Apr 2023 0:06] Filipe Silva
Cannot be fixed. MySQL 8.0 removed the table mysql.proc. Consulting Information_Schema is the only alternative.