Bug #91585 “dead” code inside the stored proc or function can significantly slow it down

• Submitted: 10 Jul 2018 1:16
• Modified: 10 Jul 2018 9:39
• Reporter: Alexander Rubin
• Status: Verified
• Category: MySQL Server: Stored Routines
• Severity: S5 (Performance)
• Version: 5.7, 8.0, 8.0.11, 5.7.23
• OS: Any
• CPU Architecture: Any
• Tags: performance, stored function, stored procedure, trigger

[10 Jul 2018 1:16] Alexander Rubin

Description:
 “dead” code (the code in a branch which will never run) can still significantly slow down the response time of a function/procedure/trigger.

A stored procedure with 4 if statements which will never be true (so the code inside will never execute, "dead" code) will run 2x slower compared to the stored procedure with no if statement. According to the (attached) ps_trace result it actually run sql/THD::LOCK_thd_data and opening_tables for each sp/jump_if_not

How to repeat:
Create 3 functions (it does not matter what will be executed inside or if it exist or not):

Function 1:

CREATE DEFINER=`root`@`localhost` FUNCTION `func1`() RETURNS int(11)
BEGIN
	declare r int default 0;
RETURN r;
END

Function 2:

CREATE DEFINER=`root`@`localhost` FUNCTION `func2`() RETURNS int(11)
BEGIN
    declare r int default 0;
    IF 1=2
    THEN
		select levenshtein_limit_n('test finc', 'test func', 1000) into r;
    END IF;
RETURN r;
END

Function 3:

CREATE DEFINER=`root`@`localhost` FUNCTION `func3`() RETURNS int(11)
BEGIN
    declare r int default 0;
    IF 1=2 THEN
		select levenshtein_limit_n('test finc', 'test func', 1) into r;
    END IF;
    IF 2=3 THEN
		select levenshtein_limit_n('test finc', 'test func', 10) into r;
    END IF;
    IF 3=4 THEN
		select levenshtein_limit_n('test finc', 'test func', 100) into r;
    END IF;
    IF 4=5 THEN
		select levenshtein_limit_n('test finc', 'test func', 1000) into r;
    END IF;
RETURN r;
END

Run benchmark:

mysql> select benchmark(1000000, func1());select benchmark(1000000, func2());select benchmark(1000000, func3());
+-----------------------------+
| benchmark(1000000, func1()) |
+-----------------------------+
|                           0 |
+-----------------------------+
1 row in set (2.48 sec)

+-----------------------------+
| benchmark(1000000, func2()) |
+-----------------------------+
|                           0 |
+-----------------------------+
1 row in set (3.53 sec)

+-----------------------------+
| benchmark(1000000, func3()) |
+-----------------------------+
|                           0 |
+-----------------------------+
1 row in set (5.69 sec)

[10 Jul 2018 1:18] Alexander Rubin

Func1,a trace with ps_trace_thread() procedure

Attachment: func1.png (image/png, text), 333.83 KiB.

[10 Jul 2018 1:18] Alexander Rubin

Func2, a trace with ps_trace_thread() procedure

Attachment: func2.png (image/png, text), 333.00 KiB.

[10 Jul 2018 1:18] Alexander Rubin

Func3, a trace with ps_trace_thread() procedure

Attachment: func3.png (image/png, text), 460.01 KiB.

[10 Jul 2018 9:39] MySQL Verification Team

Hello Alexander,

Thank you for the report.

Thanks,
Umesh