Bug #72728 C/NET slow querying procedures
Submitted: 23 May 2014 8:30 Modified: 23 May 2014 8:32
Reporter: Andrew Dalgleish Email Updates:
Status: Verified Impact on me:
None 
Category:Connector / NET Severity:S3 (Non-critical)
Version:6.8.3 OS:Any
Assigned to: CPU Architecture:Any
Triage: Needs Triage: D3 (Medium)

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

Generates a query like this:
SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA LIKE 'test' AND ROUTINE_NAME LIKE 'proc_2';

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 ?