MySQL Bugs: #32053: Stored procedures/functions are slow

Bug #32053	Stored procedures/functions are slow
Submitted:	2 Nov 2007 12:37	Modified:	8 Nov 2007 3:16
Reporter:	Yurij Zagrebnoy	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S4 (Feature request)
Version:	5.0.45	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	functions, procedures, slow, stored

Description:
Recently I had a project where I needed to make some calculations within stored procedures and noticed that they are slow compared to other interpreted languages. For such kind of applications it's very important, besides competitive products perform faster (MS SQL Server 2005 can offer stored procedures written for .NET, they are incredible fast as to calculations). 

Particularly I noticed that using local variables significantly slows down SPs and functions, also it seems that procedure/function call itself is slow.

Do you plan to do something to improve their performance?

My example: 
CREATE FUNCTION test(a INT, b INT, c INT) RETURNS double NO SQL
BEGIN
  DECLARE d, e DOUBLE;
  
  SET d = (a + b) / c;
  SET e = FLOOR(d);
  RETURN e;
END;

SELECT BENCHMARK(100000, test(1, 2, 3));

On my computer this function executes 6 times slower than its analog in PHP: 

function test($a, $b, $c) {
  $d = ($a + $b) / $c;
  $e = floor($d);
  return $e;
}

for ($i = 0; $i < 100000; $i++)
  test(1, 2, 3);

How to repeat:
see example above

Suggested fix:
Optimize interpreter.

Maybe add support for stored procedures written in Java with callback interfaces to be able to use MySQL functionality within them. I'm sure that this will highly increase MySQL reputation. Furthermore it's not so hard to implement - users already have Java VM, development environment and skills, all that is required is just interface to Java VM.

Thank you for a feature request. Please, check if UDFs (written in C) that are in MySQL for ages can help in your case. They should give you PHP/native performance for computation. 

Please, read http://dev.mysql.com/doc/refman/5.0/en/adding-functions.html for the details.

Thank you for your response, I'm quite familiar with UDFs but it's not always convenient or even possible to use them, I have many examles proving this. It would be really valuable to have stored procedures' execution speed increased.

It's hard to disagree with thisfeature request. Surely it would be nice to have stored procedures running faster. We may get Java/Lua/PHP/.Net stored procedures eventually, or just improved current implementation. 

I can not tell you for sure how and when it will be implemented, though.