| 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: | |
| Category: | Connector / NET | Severity: | S3 (Non-critical) |
| Version: | 6.8.3 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Triage: | Needs Triage: D3 (Medium) | ||
[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 ?
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.