I'm currently, for speed, using execute, bind_columns, fetch.
However, the number of columns I will be fetching is going to
increase. This is a pita for defining bind variables etc.
So, given that speed is paramount, should I continue with this
method or should I switch to fetch hashref?
Thx
John
--5UGlQXeG3ziZS81+
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable
On 2006-11-06 11:58:44 -0000, john@firstb2b.net wrote:
> I'm currently, for speed, using execute, bind_columns, fetch.=20
> However, the number of columns I will be fetching is going to=20
> increase. This is a pita for defining bind variables etc.
> So, given that speed is paramount, should I continue with this=20
> method or should I switch to fetch hashref?
This is a question only you yourself answer.
Rewrite your code to use hashref.
*Measure* how much slower it is.
Decide whether the performance is sufficient for your purposes.
hp
--=20
_ | Peter J. Holzer | If I wanted to be "academically correct",
|_|_) | Sysadmin WSR | I'd be programming in Java.
| | | hjp@wsr.ac.at | I don't, and I'm not.
__/ | http://www.hjp.at/ | -- Jesse Erlbaum on dbi-users
--5UGlQXeG3ziZS81+
Content-Type: application/pgp-signature
Content-Disposition: inline
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)
iQDQAwUBRU80qVLjemazOuKpAQJMkwXUC3XSxaTkKGpKR6JwUX/TUbHucEjr GhDU
/WdCZOM6Qtlt7jakiPfe1LbTdpvbE2d4IIh8Mq+qedyOrAsLQhKGSavAgPcN 2F/Z
kAOI23IIahHiYWxJ9tm6HfMDbuc9GFOMHd4B7s1opWRo8I8K4gbwk57DoclU 236c
A89zbb7d+8rT1nynhEqT63bdx5oCbb366CSoEttiEnVZToNtp9ec2Pn9FQ0J i4Ys
o9ICuK7idg+yaqEHtopv4MQdow==
=tIyj
-----END PGP SIGNATURE-----
--5UGlQXeG3ziZS81+--
On 6 Nov 2006 at 14:12, Also Sprach Peter J. Holzer:
> On 2006-11-06 11:58:44 -0000, john@firstb2b.net wrote:
> > I'm currently, for speed, using execute, bind_columns, fetch.
> > However, the number of columns I will be fetching is going to
> > increase. This is a pita for defining bind variables etc.
> > So, given that speed is paramount, should I continue with this
> > method or should I switch to fetch hashref?
>
> This is a question only you yourself answer.
>
> Rewrite your code to use hashref.
>
> *Measure* how much slower it is.
I did that just after sending my email. Bizarrely enough, the hashref _is_ faster by
nearly a second (3.2 secs to nearly 2.2). I can't believe there should be that much
difference.
_But_ I can't give it a proper test until the server load is more stable, probably late at
night.
I was just wondering if anyone had checked it before and if there was a cut off point
where the number of vars makes the difference negligible. I wouldn't have thought
so, but I thought I'd ask.
John
On Mon, Nov 06, 2006 at 02:12:09PM +0100, Peter J. Holzer wrote:
> On 2006-11-06 11:58:44 -0000, john@firstb2b.net wrote:
> > I'm currently, for speed, using execute, bind_columns, fetch.
> > However, the number of columns I will be fetching is going to
> > increase. This is a pita for defining bind variables etc.
> > So, given that speed is paramount, should I continue with this
> > method or should I switch to fetch hashref?
>
> This is a question only you yourself answer.
>
> Rewrite your code to use hashref.
>
> *Measure* how much slower it is.
>
> Decide whether the performance is sufficient for your purposes.
Exactly. Also keep in mind the combined approach using bind_columns:
$sth->execute;
my %row;
$sth->bind_columns( \( @row{ @{$sth->{NAME_lc} } } ));
while ($sth->fetch) {
print "$row{region}: $row{sales}\n";
}
Tim.
--bygAmIonOAIqBxQB
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable
On 2006-11-06 14:37:20 -0000, john1@firstb2b.net wrote:
> On 6 Nov 2006 at 14:12, Also Sprach Peter J. Holzer:
> > On 2006-11-06 11:58:44 -0000, john@firstb2b.net wrote:
> > > I'm currently, for speed, using execute, bind_columns, fetch.=20
[...]
> > > So, given that speed is paramount, should I continue with this=20
> > > method or should I switch to fetch hashref?
> >=20
> > This is a question only you yourself answer.
> >=20
> > Rewrite your code to use hashref.
> >=20
> > *Measure* how much slower it is.
>=20
> I did that just after sending my email. Bizarrely enough, the hashref _is=
_ faster by=20
> nearly a second (3.2 secs to nearly 2.2). I can't believe there should be=
that much=20
> difference.
I'm not very surprised. I haven't done any statistics, but I get the
feeling that when somebody says "I am doing A instead of B, because A
is faster" the there is a chance of more than 50% that B is actually
faster.
> I was just wondering if anyone had checked it before and if there was
> a cut off point where the number of vars makes the difference
> negligible.
I suspect that depends on the the perl version, the DBI version, the
processor, maybe the DBD driver and almost certainly on the data. So any
measurements would probably have been only of limited use.
hp
--=20
_ | Peter J. Holzer | If I wanted to be "academically correct",
|_|_) | Sysadmin WSR | I'd be programming in Java.
| | | hjp@wsr.ac.at | I don't, and I'm not.
__/ | http://www.hjp.at/ | -- Jesse Erlbaum on dbi-users
--bygAmIonOAIqBxQB
Content-Type: application/pgp-signature
Content-Disposition: inline
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)
iQDQAwUBRU9atFLjemazOuKpAQJAtAXUDKmo29wZMgEX2cx9rrGFwHP2sf45 x+hK
EQqcGzMaXqho2L3XKyQ00z3lXQqUfwQmxPv52AsDs6pABsav3yzNf3TPH7k6 5Npq
UoDtGEPQTbmPzQMKf8JzlplWsFCb6NSIVWwc4qD5SWjePx/g9GsQmNPNvJKM lGSO
SIvN6kHZpRgOP0FXt7I2lezGYcKfAxspa6X76O9I5YLYBOq2K/BhDR9no1kj LLQE
lD+O+1emhTqStwNpanb/txLcXg==
=ul35
-----END PGP SIGNATURE-----
--bygAmIonOAIqBxQB--
On 6 Nov 2006 at 15:08, Also Sprach Tim Bunce:
> Exactly. Also keep in mind the combined approach using bind_columns:
>
> $sth->execute;
> my %row;
> $sth->bind_columns( \( @row{ @{$sth->{NAME_lc} } } ));
> while ($sth->fetch) {
> print "$row{region}: $row{sales}\n";
> }
Ah. Thanks. Just what I'm after!
But doesn't fetchrow_hashref do this, and more efficiently?
John
On Mon, Nov 06, 2006 at 04:08:40PM -0000, john1@firstb2b.net wrote:
> On 6 Nov 2006 at 15:08, Also Sprach Tim Bunce:
>
> > Exactly. Also keep in mind the combined approach using bind_columns:
> >
> > $sth->execute;
> > my %row;
> > $sth->bind_columns( \( @row{ @{$sth->{NAME_lc} } } ));
> > while ($sth->fetch) {
> > print "$row{region}: $row{sales}\n";
> > }
>
> Ah. Thanks. Just what I'm after!
> But doesn't fetchrow_hashref do this, and more efficiently?
No. On each call to fetchrow_hashref it has to FETCH the NAME attribute
to get the keys to use for the hash. That's what makes fetchrow_hashref
significantly slower than non-hash fetching.
See the bottom half of page 24 in
http://backpan.perl.org/authors/id/T/TI/TIMB/DBI_AdvancedTal k_200608.pdf
for a (slightly obtuse) comparison of selectall_arrayref vs
selectall_hashref vs prepare+execute+fetch loop.
Tim.