[ILUG] MySQL DBI utility functions

[Niall O Broin]

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.

> [ @$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 !



Niall