Beefy Boxes and Bandwidth Generously Provided by pair Networks
We don't bite newbies here... much
 
PerlMonks  

Re: Re: Re: Re: mod_perl was hosed by a bind variable

by tantarbobus (Hermit)
on Jan 30, 2004 at 22:49 UTC ( #325436=note: print w/replies, xml ) Need Help??


in reply to Re: Re: Re: mod_perl was hosed by a bind variable
in thread mod_perl was hosed by a bind variable

Not to be a pedant, but prepare() can be faster than prepare_cached() 56% faster, in fact ;)
use DBI; use Data::Dumper; use Benchmark q(:all); use strict; my $dbh = DBI->connect("dbi:mysql:test","root","", {RaiseError=>1}) || + die $DBI::errstr; cmpthese( 9999, { cache => 'prepcache', normal => 'prep', }); sub prepcache { $dbh->prepare_cached("X"x100_000); } sub prep { $dbh->prepare("X"x100_000); }
        Rate  cache normal
cache  365/s     --   -36%
normal 569/s    56%     --

Replies are listed 'Best First'.
Re: Re: Re: Re: Re: mod_perl was hosed by a bind variable
by perrin (Chancellor) on Jan 31, 2004 at 00:08 UTC
    Uh, your SQL query is 100,000 characters long? If I change your benchmark to use "select foo from bar where baz = ?", prepare_cached is 333% faster. I'm thinking my test is a bit more realistic.

      Your test might be more realisitic; however, your original statement was 'The fastest way to use DBI is with prepare_cached() and bind variables.' which, as proved by the ealier posted counter-example, is false. That was my only point -- that there exists an input that causes your stament to be false, nothing more. Your statement would probably be better worded something like 'The fastet way to use DBI is normally with prepare_cached and bind variables' or 'The fastet way to use DBI is with prepare_cached() and bind_variables except for when it is not' ;-)

      Like I said in my origional post, I am just nitpicking, being a pedant, as it were.

        I do understand, but when your goal is to help people learn something, the things you leave out are as important as the things you put in. The example you gave is pretty absurd, and I'm skeptical that MySQL would even accept a SQL statement that long. It's not a reasonable use case.
Re^5: mod_perl was hosed by a bind variable
by dws (Chancellor) on Jan 31, 2004 at 02:33 UTC
    Not to be a pedant, but prepare() can be faster than prepare_cached() 56% faster, in fact ;)

    To be a pedant, your benchmark is highly misleading. prepare() might be faster, but the speed gain is barely visible when you actually execute the query. When you're using a database that supports prepared queries (e.g., Oracle), the cached form is a win. In the example you show, no communication with the server happens, since current versions of MySQL don't support prepared queries. What you're seeing is bookkeeping overhead.

      I don't think my benchmark is misleading. I set out to show that in at least one case prepare() CAN be faster than prepare_cached(), to provide a counter example, as it were. The benchmark that I posted shows that in some cases prepare() CAN be faster than prepare_cached() nothing more, nothing less.

      Now, prepare_cached()is normally faster and I am not saying that it is not, but there a point where the overhead of calculating the hash and doing the hash lookup takes more CPU time than it does for DBD::mysql to walk the string looking for placeholders.

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others drinking their drinks and smoking their pipes about the Monastery: (4)
As of 2019-10-19 04:55 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    Notices?