MySQL Bugs: #36694: bad information_schema performance in .NET app running stored routines

Bug #36694	bad information_schema performance in .NET app running stored routines
Submitted:	13 May 2008 18:20	Modified:	11 Jul 2008 18:34
Reporter:	Bogdan Degtyariov
Status:	Closed
Category:	Connector/Net	Severity:	S3 (Non-critical)
Version:		OS:	Microsoft Windows
Assigned to:	Bogdan Degtyariov	Target Version:
Tags:	stored procedure, .net, performance, information_schema

Description:
.NET application demonstrates appalling delays when running stored procedures. These
delays are not significant for a small number of stored procedures defined for a
database. However, once the number is really big (>5000), every call takes not less than
800-1000 milliseconds. Also, the delays occur only for the first call of the procedure
not yet executed within the current connection. If the application runs different
procedures it has to wait for 1000 ms for every call, which is more than 1,5 hours. 

All this time Connector/NET is waiting on metadata from information_schema (actually it
is a "SELECT * FROM INFORMATION_SCHEMA..." query). Before running the stored procedure
C/NET has to check IN/OUT parameters and initialize hash structures using the metadata.
Consequently, the server query log contains lots of slow queries selecting metadata as
follows:

SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA='test9' AND
ROUTINE_NAME='spa3470'

Execution time is 1020 ms.

The same data can be retrieved from mysql.proc table, but execution time is 20 ms - more
than 50 times faster!

Even though there is a workaround that helps .NET applications overcoming the stored
procedures performance issues, the problem is in the server implementation of
information_schema (so, later this report has to be qualified as server/I_S bug).

How to repeat:
1. Create 10000 stored procedures. Procedures can do the same, but should have different
names. For example:

DELIMITER $$

CREATE PROCEDURE `spa1001`(IN param1 INT, OUT param2 INT)
BEGIN 
SELECT COUNT(*) INTO param2 FROM testtab where id>param1;
END$$

DELIMITER ;

2. Run the procedure using MySqlCommand class instance:

  cmd = new MySqlCommand();
  cmd.Connection = conn;
  cmd.CommandText = "spa1001";
  cmd.CommandType = CommandType.StoredProcedure;
  cmd.Parameters.Add("?param1", "3");
  cmd.Parameters["?param1"].Direction = ParameterDirection.Input;

  cmd.Parameters.Add("?param2", MySqlDbType.Int32);
  cmd.Parameters["?param2"].Direction = ParameterDirection.Output;

  cmd.ExecuteNonQuery(); // This line takes 1000 ms to accomplish

Suggested fix:
Connector/NET has an option "Use Procedure Bodies" indicating that the user connecting to
the database havs the SELECT privileges for the mysql.proc (stored procedures) table. So,
if the option is set to TRUE, it is safe to query mysql.proc directly and take the
advantage of greater performance.

The patch is uploaded below.

Patch from "\tags\5.2.1\"

Attachment: patch36694.diff (application/octet-stream, text), 5.39 KiB.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/47005

Fixed in 5.2.3.  Here is the change log entry

Changed how the procedure schema collection is retrieved.  If 'use procedure bodies=true'
then we select on the mysql.proc table directly as this is up to 50x faster than our
current IS implementation.  If 'use procedure bodies=false', then the IS collection is
queried.

Are 'use procedure bodies=true' and 'use procedure bodies=false' new options? How are
these set by the user? Is there a small piece of example code that can illustrate this
please?

They are connection string options.  They are set by adding them to your connection
string.  Here is an example:

string connStr = "server=localhost;user id=root;database=test;use procedure
bodies=true";
MySqlConnection c = new MySqlConnection(connStr);
c.Open();

An entry has been added to the 5.2.3 changelog:

Changed how the procedure schema collection is retrieved. If the connection string
contains “use procedure bodies=true” then a select is performed on the mysql.proc
table directly, as this is up to 50 times faster than the current Information Schema
implementation. If the connection string contains “use procedure bodies=false”, then
the Information Schema collection is queried.

More comments and full test case in Bug#36376