MySQL Forums :: Stored Procedures :: Optimization of stored function overhead

Beat Vontobel wrote:

> Tanks for your clarification and your tests. I won't trust DO BENCHMARK too much from now on
> but test performance issues in a loop as you did.

I'm afraid there's more going on than DO BENCHMARK issues.

From the code below I get:

UPDATE with builtin function: 0.08 sec
UPDATE with stored function: 0.79 sec (x 10)

SET with builtin function: 1.17 sec
SET with stored function: 2.02 sec (x 1.7)

Any ideas?

____
DELIMITER //
USE test//
DROP PROCEDURE IF EXISTS fillup//
CREATE PROCEDURE fillup(IN rows INT)
BEGIN
DROP TABLE IF EXISTS t1, t2;
CREATE TABLE t1 (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, d CHAR(26) NOT NULL) ENGINE = Memory;
CREATE TABLE t2 (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, d CHAR(26) NOT NULL) ENGINE = Memory;
fill: REPEAT

INSERT INTO t1 (d) VALUES ('abcdefghijklmnopqrstuvwxyz');
INSERT INTO t2 (d) VALUES ('abcdefghijklmnopqrstuvwxyz');
SET rows = rows - 1;
UNTIL rows = 0
END REPEAT fill;
END //

DROP FUNCTION IF EXISTS MYSUBSTRING //
CREATE FUNCTION MYSUBSTRING(d CHAR(26)) RETURNS CHAR(26) RETURN SUBSTRING(d, 10, 10) //

DROP PROCEDURE IF EXISTS SETSUBSTRING //
CREATE PROCEDURE SETSUBSTRING(i INT)
BEGIN
DECLARE s CHAR(26);

WHILE i > 0 DO
SET s = SUBSTRING('abcdefghijklmnopqrstuvwxyz', 10, 10);
SET i = i - 1;
END WHILE;
END //

DROP PROCEDURE IF EXISTS SETMYSUBSTRING //
CREATE PROCEDURE SETMYSUBSTRING(i INT)
BEGIN
DECLARE s CHAR(26);

WHILE i > 0 DO
SET s = MYSUBSTRING('abcdefghijklmnopqrstuvwxyz');
SET i = i - 1;
END WHILE;
END //


DELIMITER ;

CALL fillup(100000);

UPDATE t1 SET d = SUBSTRING(d, 10, 10);
UPDATE t2 SET d = MYSUBSTRING(d);
CALL SETSUBSTRING(100000);
CALL SETMYSUBSTRING(100000);

DROP TABLE t1, t2;

--
felix
Please use BBCode to format your messages in this forum.