MySQL queries are fast when run directly but really slow when run as stored proc

Question

I've been trying to figure out what's wrong with a set of queries I've got and I'm just confused at this point.

It's supposed to be in a stored procedure which gets called by a GUI application.

There's only one "tiny" problem, it's first a simple UPDATE, then an INSERT using a SELECT with a subselect and finally another UPDATE. Running these queries together by hand I get a total execution time of 0.057s, not too shabby.

Now, I try creating a stored procedure with these queries in it and five input variables, I run this procedure and on the first attempt it took 47.096s with subsequent calls to it showing similar execution times (35 to 50s). Running the individual queries from the MySQL Workbench still show execution times of less than 0.1s

There really isn't anything fancy about these queries, so why is the stored procedure taking an eternity to execute while the queries by themselves only take a fraction of a second? Is there some kind of MySQL peculiarity that I'm missing here?

Additional testing results:

It seems that if I run the queries in MySQL Workbench but use variables instead of just putting the values of the variables in the queries it runs just as slow as the stored procedure. So I tried changing the stored procedure to just use static values instead of variables and suddenly it ran blazingly fast. Apparently for some reason using a variable makes it run extremely slow (for example, the first UPDATE query goes from taking approximately 0.98s with three variables to 0.04-0.05s when I use the values of variables directly in the query, regardless of if it's in the stored procedure or running the query directly).

So, the problem isn't the stored procedure, it's something related to my use of variables (which is unavoidable).

Answer 1

Since I didn't want to waste too much time trying to figure out why using variables in my stored procedures made them extremely slow I decided to employ a fix some people would consider quite ugly. I simply executed each query directly from the data access layer of my application. Not the prettiest way to do it (since a lot of other stuff for this app uses stored procedures) but it works and now the user won't have to wait 40+ seconds for certain actions as they happen almost instantly.

So, not really a solution or explanation of what was happening, but at least it works.

Answer 2

I had a similar problem. Running a mysql routine was horrible slow. But a colleague helped me. The problem was that AUTOCOMMIT was true; So every insert into and select was creating a complete transaction. Then I run my routine with

SET autocommit=0; 

at the beginning and

SET autocommit=1;                    

at the end. The performance went from nearly 500s to 4s