Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.
share|improve this question

1 Answer 1

up vote 0 down vote accepted

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

share|improve this answer
    
Thanks so much. –  briankip Apr 4 '12 at 9:24

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.