Friday, February 13, 2009

Are MySQL stored procedures slow?

 

Yes, if compared to code in Java or C#. For example, this overly simple code took 284 seconds.

CREATE PROCEDURE CountTest()
begin
    declare counter int default 0;
    select now();
    repeat
        set counter = counter + 1;
    until counter > 120000000
    end repeat;
    select counter;
    select now();
end

Ignoring my off by one error, here is equivalent code in C# (the language I’m currently learning).  It took 419 milliseconds, or MySQL took 677 times longer to execute. From my experience, Java isn’t going to be any slower.

 int counter = 0;
while (counter < 120000000)
{
    counter++;
}

Slow stored procedure performance is one of the reasons why it usually isn’t wise to implement computationally expensive business logic in the database.   With networks not being the bottleneck they once were it is often better to extract the data to the application layer and process it in the programming language of your choice, even if that takes a few extra round trips to the database.  There are exceptions where those extra round trips are too numerous and prohibitively expensive.  With something as complex as a database there are always exceptions.

This example is a bit too simple, but, based on experience, it is representative of the general performance of stored procedure logic.

How can MySQL get away with such poor performance? Well, the equivalent code in MS SQL Server took 80 seconds on the same hardware, which is also two orders of magnitude slower than C# or Java code. 

create procedure testCount
as
begin
    declare @countNo int;
    set @countNo = 0;
    while (@countNo < 120000000)
    begin
        set @countNo = @countNo + 1
    end
    select @countNo
end

MS SQL Server code runs 3.5 times faster than MySQL, but given how much older MS SQL Server is, MySQL is doing well here.  So are MySQL Stored procedures slow?  Not really, if compared to other databases I’ve used.

Just don’t use them to do computationally expensive business logic.

5 comments:

Ivan Novick said...

I dont think these are equivalent because you are issuing SELECT calls rather than just incrementing a value.

The important question would be to compare the stored procedure with issuing the same SELECT calls from programming langauge

Morgan said...

I did a simple benchmark on this a while ago. Yep, they're slow:

http://mtocker.livejournal.com/45222.html

dbscience said...

An example of computationally expensive code would be a loop in the form of a cursor over a large amount of data doing complex business logic. Stored procs are typically slower than other code in this case.

A simple procedure only comprised of a select statement is another matter. Then, the comparison would be between a stored proc and a parameterized query. Perhaps I should test that case as well.

I know in more mature databases there isn't much of a performance difference between stored procs and parameterized SQL, but MySQL's plan caching isn't as robust as more mature databases for both cases so I'm not sure what the difference would be.

Tomas Thalmann said...

I totally agree with Ivan. You should compare a code snippet calling some statements directly to one that calls a stored procedure.

dbscience said...

I added a select in the next blog post. The results didn't change.