MySQL Forums
Forum List  »  Stored Procedures

Slow stored procedure call
Posted by: Bill Gladstone
Date: July 30, 2013 07:48PM

I'm trying to figure out why a call to a simple stored procedure is taking much longer than it takes to directly execute the single SELECT statement the stored procedure contains.

I'll include the DDL below my signature, but the problem is that this call made from MySQL Workbench:

call GetUrl('003d6ae5a7c62b5995cb555f3784af55')

...takes about 0.5 seconds to execute. That's a long time for what the procedure does, which is to get an URL from a table with about 200,000 URLs using this SELECT statement:

SELECT * from Urls WHERE HashedUrl = hashedUrlParam

If I run that SELECT statement directly, it takes "0.000 sec", according to MySQL Workbench. That's more like the performance I want!

The GetUrl() call originally took 2.5 seconds. I changed innodb_buffer_pool_size from 23M to 1500M and that brought the time down to 0.5 seconds, but further increases in that setting didn't improve things.

Is there some other setting I should change to fix this? This instance of MySQL was originally installed on a small Amazon EC2 instance with 1.75 GB of memory, and I suspect the MySQL setup program adjusted the settings accordingly. The Amazon instance is now medium and has more memory (3.75 GB), so maybe the my.ini file needs further tuning?

(By the way, stored procedures are a requirement where I work, so I can't execute the SELECT statement directly.)

Thanks!

-- Bill


[Is this a good format for posting DDL? Sorry, I'm new to MySQL.]

This is MySQL 5.6 on 64-bit Windows Server 2008.

-- -----------------------------------------------------
-- Table `Collections`.`Urls`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `Collections`.`Urls` (
`HashedUrl` CHAR(32) CHARACTER SET 'ascii' COLLATE 'ascii_general_ci' NOT NULL
`Url` TEXT CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_bin' NOT NULL,
`ExpandedUrl` TEXT CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_bin' NULL,
PRIMARY KEY (`HashedUrl`) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- procedure GetUrl
-- -----------------------------------------------------

DELIMITER $$
USE `Collections`$$
CREATE PROCEDURE `Collections`.`GetUrl`
(
hashedUrlParam char(32)
)
BEGIN

SELECT * from Urls
WHERE HashedUrl = hashedUrlParam;

END
$$

DELIMITER ;



Edited 1 time(s). Last edit at 07/30/2013 07:49PM by Bill Gladstone.

Options: ReplyQuote


Subject
Views
Written By
Posted
Slow stored procedure call
8865
July 30, 2013 07:48PM
2906
August 01, 2013 10:24PM
2532
August 19, 2013 10:24AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.