[ILUG] MySQL DBI utility functions

[Fergal Daly]

On Thu, Sep 13, 2001 at 03:34:29PM +0100, Niall O Broin wrote:
> On Thu, Sep 13, 2001 at 11:47:40AM +0100, Fergal Daly wrote:
> 
> > If you moved the prepare to outside the first loop it might make a
> > difference, the whole point of a prepare is that the DB only has to parse
> > the statement once and you can reuse it without reparsing. I think this may
> > not work very well for MySQL but even still moving it outside might help.
> > It's possible that you'd see a bigger difference with Oracle where the
> > prepares can have much bigger effects, especially with complex queries.
> 
> Yes, but then I wouldn't be comparing like with like. The repetition was
> only to take sufficient time to benchmark.

I misread your email (and skipped the benchmarks altogether!) I thought they
were coming out the same and you wanted to make the roll-your-own faster.

Your code is leaving out a call to bind, granted it's a call with no
arguments but it might have a significant effect given that it probably
result in communication with the DB server. Try adding 

$sth->bind;

before the execute in your loop. I reckon you should also move the
disconnects outside the timed sections.

> > [ @$row ] creates an anonymous ref to an array that has the same entries as
> > @$row. Basically it makes a copy of the array. Possibly the array pointed to
> > by $row is permanently associated with that prepared statement and is used
> > to pass values from C code to perl code. That would mean it gets altered
> > every time a new row is read from the DB and fetch always returns the same
> > ref. You could end up with
> > 
> > push @rows, $row while($row = $sth->fetch);
> > 
> > filling @rows with n references to the same array
> 
> That's exactly it ! The DBI code says that fetch always returns the same
> array ref, so this copying is because of that. I think this points to the
> speed difference too - I believe I do a little less data copying by calling
> fetch (fetchrow_arrayref) myself repeatedly in a loop than the utility
> functions do. Full marks to Fergal !

Do I get a sucky sweet? (in case you've never seen D'Unbelievables, that's
not a come on!).

The cost of copying a 1 element array is going to be ignorable. I think it's
the lack of a bind that makes the difference,

Fergal