Search This Blog

13 February 2009

Re: Are MySQL stored procedures slow?

In Brooks Johnson's blog posting, Database Science: Are MySQL stored procedures slow?, he mentioned how much slower MySQL's stored procedures are and then compared it with a small piece of .Net code.

Using Perl, a stored procedure which counts to the same value is obviously not going to be as fast as bytecode languages with JIT compilers but it is a lot faster than MySQL's native SQL stored procedures. These perl stored procedures are able to perform dynamic SQL using the familiar DBD::mysql driver without any risk of self-deadlock.

Of course, you can also write stored procedures in Java for many databases but I haven't yet written the neccessary Type 2 JDBC driver to perform a in-thread connection back into the database server to be able to do sophisticated work with MySQL.

mysql> create procedure loopy() no sql dynamic result sets 1 language perl external name "Loopy::benchmark";
Query OK, 0 rows affected (0.00 sec)

mysql> call loopy();
+--------------+
| Time Elapsed |
+--------------+
| 11 |
+--------------+
1 row in set (11.59 sec)

Query OK, 0 rows affected (11.60 sec)

Here is the perl module...

package Loopy;

use 5.008008;
use strict;
use warnings;
use Symbol qw(delete_package);
require Exporter;
our @ISA = qw(Exporter);
our @EXPORT_OK = qw( );
our @EXPORT = qw( benchmark );
our $VERSION = '0.01';

# Preloaded methods go here.

sub benchmark()
{
my %result = ();
my $start_time = time;
my $counter = 0;
while ($counter < 120000000)
{
$counter++;
}
my $end_time = time;
$result{'Time Elapsed'} = ($end_time - $start_time);
return \%result;
}

1;
__END__

No comments: