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__

WTF worthy man page suggestion...

I encountered the following text in a man page. Please tell me that I am not alone in thinking that the "typical" solution suggested is WTF worthy...

After calling "commit" or "rollback" many drivers will not let you
fetch from a previously active "SELECT" statement handle that's a child
of the same database handle. A typical way round this is to connect the
the database twice and use one connection for "SELECT" statements.

If the programmer really wants to reuse values from a SELECT statement which occurred within a transaction after the transaction is closed, they should cache those values themselves by populating some variable within their application. IMO, they definitely should not be using multiple connections to the same database from one unit of work unless they are perfectly happy to accept inconsistent data and potentially corrupted inserts and updates.

No wonder many software developers are so confused when supposedly "authoritative" manuals give them screwball advice.