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

Question

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.

Answer 1

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 !!!