MySQL user's Conference

[Bradley Baetz]

On Sat, Apr 26, 2003 at 11:43:44AM +0100, Gervase Markham wrote:
> >Perl DBI was built for speed and is pretty efficient, but you have to 
> >know how to use it right.  For speed:

> >    * try fetchall_arrayref to see if it's faster
> 
> At first glance, this seems to contradict some advice bbaetz gave me 
> about not using fetchall at all. What's the story there?

Well, it depends what you're doing. Ideally, what we should be doing is
doing the ->prepare/->execute in perl, and then passing the iterator
through into the template. That avoids all the intermediate
stuff-it-into-a-datastructure thing. TT doesn't support that, though,
and writing a TT iterator plugin is hampered by the fact that tied stuff
doesn't work with the XS stash. Plus we often have to do extra queries
anyway.

If all you're going to do is get the data, and pass it to a template,
then fetchall is better. If you're going to process it one at a time,
and templates aren't involved, then just ->fetch one by one, to avoid
materialising the data.[1] If you're going to process it one by one, but
then pass it to the template somehow (eg create a new perl object based
on the values), then I'd guess that ->fetch is still better (since the
client libraries should handle fetching data in batches anyway), but it
would need timing.

I tried to get Apache::DProf working on my mod_perl install earlier
today, so that I can get some timings w/o the load time. It didn't seem
to like Apache::Registry, though - I may have to make a handler out of
the script temporarily.

> 
> Gerv
> 

Bradley

[1] Note that in _practice_, this happens anyway - mysql grabs all the
data at once to avoid tying up the tables with read locks (by default,
anyway), and postgres' current client<->server protocol only supports
grabbing all the data at once, although thats being fixed in CVS. I
don't know if that still applies for mysql's innodb stuff, and pg is
currently fixing it (as in, wire protocol changes are in CVS, but no
client library updates to use the new functionality yet)