SPUG: idioms of late

[Skylos]

On Mon, Jan 18, 2010 at 12:04 PM, Fred Morris <m3047 at m3047.net> wrote:

>
> Another variant of that which is useful is as part of, for instance,
> enforcing a "no quotes in SQL literals" rule:
>
> my ($name, $address, $city) =
>    map $dbh->quote($_), @{$self}{qw/ name address city /};
>
> my $sql .= "VALUES ($name, $address, $city)";
>
>
If you have a rule 'no quote sin sql literals' rule, you're violating the
spirit by simply interpolating them in, don't you think?  Just because
they're not in the literal doesn't mean they're not in the string that goes
to the server.  It sounds like a poorly stated concept formed into a rule.

Regardless of that, among the reasons not to do this is the fact that the
database will try to remember your queries for optimization (queries repeat
frequently) when you do the dbh->quote method, the sql is different every
single time.   If you use binding instead, something like $dbh->do('...
VALUES (?, ?, ?)', undef, $name, $address, $city); to the database this
query looks the same regardless of what name, address, and city may be.  If
its new sql, then the db has to recompile it and recreate an execution plan
- incurring significant overhead that could have been avoided entirely had
you bound the variables.

I recently worked a project where a set of non-binding queries in rapid
succession would kill the mysql database reliably.  rewrote the queries, and
it is much happier now.

David

-- 
"If only I could get rid of hunger by rubbing my belly" - Diogenes
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.pm.org/pipermail/spug-list/attachments/20100118/3ec9ff6f/attachment.html>