MySQL Bugs: #21013: Performance Degrades when importing data that uses Trigger and Stored Procedure

Bug #21013	Performance Degrades when importing data that uses Trigger and Stored Procedure
Submitted:	12 Jul 2006 19:08	Modified:	15 Aug 2006 11:37
Reporter:	Chris Calender	Email Updates:
Status:	Closed
Category:	Server: SP	Severity:	S3 (Non-critical)
Version:	>= 5.0.18	OS:	All OS
Assigned to:	Tomash Brechko	Target Version:
Tags:	stored procedure, trigger, importing, import, degradation, degrades, performance

Description:
When performing an import on a table with a trigger that calls a stored procedure, the
performance time of this increases dramatically from version 5.0.17 to 5.0.18.

Please see the details/files I have marked as private.

In the commands below, the first step just creates the database, trigger, and stored
procedure.  We are not concerned with this step.

The second step is the problem.

I have tested this on several versions of 5.0.x.

5.0.16	< 1 second
5.0.17	< 1 second
5.0.18	~ 47 seconds
5.0.22	~ 47 seconds
5.0.23	~ 47 seconds

I have verified this on Windows, and the customer stated they experienced the same
problem on FreeBSD, Linux, and Windows.

How to repeat:
shell> mysql < test_slow_trigger.sql
shell> mysql slowtest < slowtest.sql

Suggested fix:
Not sure.  Although I first thought it was related to bug #17260, but that bug fix did
not help with this particular problem.

However, from looking at the 5.0.18 changelogs, it appears to have something to do with
the changes to how the server treats stored routine parameters and local variables (and
stored function return values) according to standard SQL.

http://dev.mysql.com/doc/refman/5.0/en/news-5-0-18.html

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/9179

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/9219

Pushed into 5.0.25

Merged into 5.1.12

Thank you for your bug report. This issue has been committed to our source repository of
that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available
version, including the bug fix. More information about accessing the source trees is
available at

    http://dev.mysql.com/doc/en/installing-source.html

Documented bugfix in 5.0.25/5.1.12 changelogs.