How to use the 'select benchmark' command on a procedure

I am trying to check which of my procedures is slowest and thus needs optimization.

I currently have around 160 procedures and about 30 functions.

I am using the select benchmark() to check the procedure speed, but all the commands I try end in the error "Check your syntax".

select BENCHMARK(1000, CALL testProc()); gives me an error,

select BENCHMARK(1000, testProc()); also gives me an error.

QUESTION

• Can Benchmark work on procedures, the examples I see only use functions.

• Is there a better, faster automated way to test execution speeds of procedures.

The BENCKMARK function only works on expressions.

What qualifies as an expression?

• Stored Function (has a return value)

• ENCODE function on a String

• 1 + 2 (that expression gives an integer)

ALTERNATIVE

What you need is to simulate the BENCHMARK function yourself.

Here is some sample code for you to try

drop database if exists mybmark;
create database mybmark;
use mybmark
DELIMITER $
DROP PROCEDURE IF EXISTS `mybmark`.`testproc` $
CREATE PROCEDURE `mybmark`.`testproc` ()
BEGIN

    DECLARE answer INT;

    SELECT 1+2 INTO answer;

END $
DELIMITER ;
DELIMITER $
DROP PROCEDURE IF EXISTS `mybmark`.`mybenchmark` $
CREATE PROCEDURE `mybmark`.`mybenchmark` (loop_count INT,expr varchar(128))
BEGIN
    DECLARE dt1,dt2,dtdiff,ndx INT;

    SET dt1 = UNIX_TIMESTAMP();
    SET ndx = loop_count;
    SET @sql = expr;
    PREPARE stmt FROM @sql;
    WHILE ndx > 0 DO
        EXECUTE stmt;
        SET ndx = ndx - 1;
    END WHILE;
    DEALLOCATE PREPARE stmt;
    SET dt2 = UNIX_TIMESTAMP();
    SET dtdiff = dt2 - dt1;

    SELECT dt1,dt2,dtdiff;

END $
DELIMITER ;

To benchmark the testproc procedure, just pass the call to the testproc procedure as a parameter. For example to call the testproc procedure 1,000,000 times, do this:

mysql> call mybmark.mybenchmark(1000000,'CALL mybmark.testproc()');
+------------+------------+--------+
| dt1        | dt2        | dtdiff |
+------------+------------+--------+
| 1333474085 | 1333474102 |     17 |
+------------+------------+--------+
1 row in set (16.80 sec)

Query OK, 0 rows affected (16.80 sec)

mysql>

Give it a Try !!!

StackExchange Database Administrators Q#15937, answer score: 4