http://qs321.pair.com?node_id=390778

A new DBD::mysql

Patrick Galbraith, senior developer at MySQL AB, has made available a modified version of DBD::mysql. It is not in the CPAN yet, (and I believe it will go there when the maintainer is satisfied with the tests) but it is usable.

What is the fuss about? This version makes a few interesting additions to this popular module:

Some documentation is available at this OSCON presentation (PDF).

Installing it

As I mentioned before, it is not on CPAN yet. You must get it from the author's CVS and fiddle a little bit to get it up and running.

The main problem is that this new version requires the client library that comes with MySQL 4.1, and if you are already using MySQL 3.23/4.0 in your box, you should install the 4.1 version, and since it is not advisable to trust new software before testing it, you shold install it without removing the working server. This is not very difficult, but not easy for the average Joe either. Therefore, some caution should be necessary. Moreover, you may need to compile the server source rather than using the binaries if your system is using old libraries (at least for the embedded server this is what I had to do).

benchmarking

This change is not easy to measure up, because the benefits of a prepared statement may vary significantly depending on the query complexity. Nonetheless, I made some measurements, and even with a simple query, the difference is visible. (Please refer to Speeding up the DBI for a detailed explanation of the profiling methods used here)

$dbh1->{ mysql_server_prepare } = 0; # emulated prepared statement $dbh2->{ mysql_server_prepare } = 1; # real prepared statement my $query = qq{select book_id from books where author_id = ? }; use DBI::Profile; $dbh1->{Profile} = DBI::Profile->new; $dbh2->{Profile} = DBI::Profile->new; $dbh1->{Profile} = 4; $dbh2->{Profile} = 4; my $sth1 = $dbh1->prepare($query); my $sth2 = $dbh2->prepare($query); timethese ( 5000, { emulated => sub { for (1 .. 15) { $sth1->execute($_); my $rows = $sth1->fetchall_arrayref(); } }, prepared => sub { for (1 .. 15) { $sth2->execute($_); my $rows = $sth2->fetchall_arrayref(); } } } ); print "emulated\n", $dbh1->{Profile}->format; print "prepared\n", $dbh2->{Profile}->format; $dbh1->{Profile} =0; $dbh2->{Profile} =0;

The benchmarking code will simply prepare two qwueries, one without using the new feature and one taking advantage of it.

Benchmark: timing 5000 iterations of emulated, prepared...
  emulated: 24 wallclock secs ( 6.88 usr +  2.52 sys =  9.40 CPU)
  prepared: 20 wallclock secs ( 6.84 usr +  2.31 sys =  9.25 CPU)

The result shows that there is a (little) convenience with the true prepared statements. The real difference, though, comes when we analyze the profiling output.

emulated
DBI::Profile: 22.655414s (150003 calls) benchdbd.pl @ 2004-09-14 10:31:07
'FETCH'             => 0.000006s
'STORE'             => 0.000237s
'execute'           => 20.919974s / 75000 = 0.000279s avg 
'fetchall_arrayref' => 1.735029s / 75000 = 0.000023s avg 
'prepare'           => 0.000168s

prepared
DBI::Profile: 19.629611s (150003 calls) benchdbd.pl @ 2004-09-14 10:31:07
'FETCH'             => 0.000007s
'STORE'             => 0.000103s
'execute'           => 17.339136s / 75000 = 0.000231s avg 
'fetchall_arrayref' => 2.289909s / 75000 = 0.000031s avg 
'prepare'           => 0.000456s

Notice first that the 'prepare' method takes longer when using the real prepared statements. This is because the emulated 'prepare' is just a way of passing parameters, and it does not call the dataabse server at all. The difference is all in the 'execute' method, where the emulated version takes 15% longer than the new implementation.

Update Sep 14, 2004 at 11:23 GMT+1
I also noticed that fetchall_arrayref is taking longer but I forgot to mention it here. (Thanks to demerphq for reminding me).
I assume (but this is really a wild guess, mind you) that this is due to the larger data structure needed for prepared statement that is being released at this moment.
Anyway, hopefully the author is going to look at this page and if an explanation exists it will come up eventually.

 _  _ _  _  
(_|| | |(_|><
 _|