Beefy Boxes and Bandwidth Generously Provided by pair Networks
Don't ask to ask, just ask

Re: Re: Re:{2} Leashing DBI

by pope (Friar)
on Jun 28, 2001 at 03:52 UTC ( #92125=note: print w/replies, xml ) Need Help??

in reply to Re: Re:{2} Leashing DBI
in thread Leashing DBI

prepare_cached() (and other similar tricks of reusing the same statement handle to avoid reparsing the same SQL statement) doesn't give any performance benefit if the underlying database doesn't support that, i.e. MySQL.

For DBD::mysql, when we call $sth->execute, the driver will eventually call mysql_real_query() which reparses the same SQL statement. Placeholders are emulated by the driver.
The following is a simple test which compares the performance between using of reusable $sth and using the "unrecommended" way. With DBD::mysql, of course:

my $res = $dbh->selectall_arrayref(<<'SQL'); SELECT id, title FROM tbl_articles SQL my @subs = ( [ 'Using string subst' => sub { for (@$res) { $dbh->do( 'UPDATE tbl_articles SET title = '. $dbh->quote('##'.$_->[1])." WHERE id = $_->[0]"); } }, ], [ 'Using placeholders' => sub { my $sth = $dbh->prepare(<<'SQL'); UPDATE tbl_articles SET title = ? WHERE id = ? SQL for (@$res) { $sth->execute(@{$_}[1,0]); } }, ], ); for (@subs) { print "$_->[0]:\n"; timethis(100, $_->[1]); }
And my result:
Using string subst: timethis 100: 15 wallclock secs ( 2.33 usr + 0.78 sys = 3.11 CPU) Using placeholders: timethis 100: 15 wallclock secs ( 2.45 usr + 0.97 sys = 3.42 CPU)

So if we stuck to MySQL, we needn't worry about placholders, prepare_cached() or something like that. No performance penalty for not harnessing them.

I agree with you about sacrificing performance for 'beauty' of code. This even worse for wrapper modules (around DBI) which try to add more database independency than that provided by DBI. Take a look of how DBIx::Recordset solves the problem of alleviating different ways of each DBMS of doing partial select. Really unsatisfactory from performance standpoint.

Replies are listed 'Best First'.
Re: Re: Re: Re:{2} Leashing DBI
by Masem (Monsignor) on Jun 28, 2001 at 04:00 UTC
    First, it's not that placeholders provide a performance boost (I'd expect a decline, actually, but...).. but they *are* important in the detainting and security of your database. Even if your server doesn't support placeholders, this is still the sanest thing to use when you can, since if the server doesn't have placeholders, the functionality is replicated by the appropriate DBI glue. This also applies to prepare_cached(); if the database server doesn't support it, it will do it's best to handle it in any case; in cases where it doesn't, it simply will pass prepare_cached() to prepare().

    But in the situation I was talking about, I was more thinking about the penalty of creating a hash to pass, parsing the SQL, accessing the hash and calling prepare & execute all in a separate area, as opposed to calling prepare & execute without extra baggage. AGain, I doubt it's much, and the small hit may be more than outweighed by the ease of programming in such a fashion.

    Dr. Michael K. Neylon - || "You've left the lens cap of your mind on again, Pinky" - The Brain

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://92125]
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others cooling their heels in the Monastery: (8)
As of 2020-01-17 19:18 GMT
Find Nodes?
    Voting Booth?