Where are the selected rows stored using fetchall_arrayref

------_=_NextPart_001_01C67DF8.5B14BF98
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

Hi,=20
=20
I was wondering if someone can tell me how, where and what Perl DBI
stores the data returned from fetchall_arrayref. For example: if I
issues a SELECT statement such as "select a.col1, b.col2, c.col3 from
someTable1 a, someTable2 b, someTable3 c where someCondition". From
this I get an array reference. I understand that the array references
are pointers, but where are these pointers stored.
=20
I will be using Perl DBI to dump approximately five billion rows through
a socket to another server where another program will bulk load to a
database server.
=20
Here is an example of what I am going to be doing:
=20
=20
unless ($arrayref =3D $sth->fetchall_arrayref()) {
$MESSAGE =3D "sub_fetchallArrayref() - $DBI::errstr";
$STATUS =3D $FAILURE;
sub_exit();
}
=20
my $sock =3D new IO::Socket::INET->new(PeerPort =3D> $port, Proto =
=3D>
$proto, PeerAddr =3D> $node);
if (!$sock) {
print STDERR "Unable to open socket to $node at port $port using
protocol $proto.\n";
return $FAILURE;
}
=20
foreach $row (@{$arrayref}) {
$piped =3D join($delim, @$row);
$sock->send($piped) || return $FAILURE;
}
=20
My concern is I might run out of memory. Hope someone can shed some
light.
=20
Thanks in advance.
=20
Peter


This E-mail message is for the sole use of the intended recipient(s) and =
may contain confidential and privileged information. Any unauthorized =
review, use, disclosure or distribution is prohibited. If you are not =
the intended recipient, please contact the sender by reply E-mail, and =
destroy all copies of the original message.

------_=_NextPart_001_01C67DF8.5B14BF98--

RE: Where are the selected rows stored using fetchall_arrayref

Hi Peter,

You have a valid concern regarding memory usage.

Don't use fetchall_arrayref when you are dealing with such large numbers of
rows. Some DBD's (eg DBD::Oracle) allow you to control the caching on the
client side by setting RowCacheSize (refer to DBI pod). This will give you
adequate performance without consuming all available memory. For example,
with DBD::Oracle, around 500 gives you good bang for buck (unless your rows
contain LOB's of course).

Steve

> -----Original Message-----
> From: Loo, Peter # PHX [mailto:Peter.Loo@source.wolterskluwer.com]
> Sent: Tuesday, 23 May 2006 9:35 AM
> To: dbi-users@perl.org
> Subject: Where are the selected rows stored using fetchall_arrayref
>
> Hi,
>
> I was wondering if someone can tell me how, where and what Perl DBI
> stores the data returned from fetchall_arrayref. For example: if I
> issues a SELECT statement such as "select a.col1, b.col2, c.col3 from
> someTable1 a, someTable2 b, someTable3 c where someCondition". From
> this I get an array reference. I understand that the array references
> are pointers, but where are these pointers stored.
>
> I will be using Perl DBI to dump approximately five billion rows through
> a socket to another server where another program will bulk load to a
> database server.
>
> Here is an example of what I am going to be doing:
>
>
> unless ($arrayref = $sth->fetchall_arrayref()) {
> $MESSAGE = "sub_fetchallArrayref() - $DBI::errstr";
> $STATUS = $FAILURE;
> sub_exit();
> }
>
> my $sock = new IO::Socket::INET->new(PeerPort => $port, Proto =>
> $proto, PeerAddr => $node);
> if (!$sock) {
> print STDERR "Unable to open socket to $node at port $port using
> protocol $proto.\n";
> return $FAILURE;
> }
>
> foreach $row (@{$arrayref}) {
> $piped = join($delim, @$row);
> $sock->send($piped) || return $FAILURE;
> }
>
> My concern is I might run out of memory. Hope someone can shed some
> light.
>
> Thanks in advance.
>
> Peter
>
>
> This E-mail message is for the sole use of the intended recipient(s) and
> may contain confidential and privileged information. Any unauthorized
> review, use, disclosure or distribution is prohibited. If you are not the
> intended recipient, please contact the sender by reply E-mail, and destroy
> all copies of the original message.

Re: Where are the selected rows stored using fetchall_arrayref

There is nothing to be gained here by using fetchall_arrayref, and
you will almost certainly have problems. You want to actually do:

while ($row = $sth->fetchrow_arrayref()) {
$sock->send(join($delim, @$row));
}

Mark

Loo, Peter # PHX wrote:
> Hi,
>
> I was wondering if someone can tell me how, where and what Perl DBI
> stores the data returned from fetchall_arrayref. For example: if I
> issues a SELECT statement such as "select a.col1, b.col2, c.col3 from
> someTable1 a, someTable2 b, someTable3 c where someCondition". From
> this I get an array reference. I understand that the array references
> are pointers, but where are these pointers stored.
>
> I will be using Perl DBI to dump approximately five billion rows through
> a socket to another server where another program will bulk load to a
> database server.
>
> Here is an example of what I am going to be doing:
>
>
> unless ($arrayref = $sth->fetchall_arrayref()) {
> $MESSAGE = "sub_fetchallArrayref() - $DBI::errstr";
> $STATUS = $FAILURE;
> sub_exit();
> }
>
> my $sock = new IO::Socket::INET->new(PeerPort => $port, Proto =>
> $proto, PeerAddr => $node);
> if (!$sock) {
> print STDERR "Unable to open socket to $node at port $port using
> protocol $proto.\n";
> return $FAILURE;
> }
>
> foreach $row (@{$arrayref}) {
> $piped = join($delim, @$row);
> $sock->send($piped) || return $FAILURE;
> }
>
> My concern is I might run out of memory. Hope someone can shed some
> light.
>
> Thanks in advance.
>
> Peter
>
>
> This E-mail message is for the sole use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply E-mail, and destroy all copies of the original message.
>

RE: Where are the selected rows stored using fetchall_arrayref

Thanks Mark and Steve B. for your comments. I will make the changes to
the program. Excellent advise.
=20
Peter Loo
Wolters Kluwer Health
(602) 381-9553

-----Original Message-----
From: markd [mailto:mark@immermail.com]=20
Sent: Monday, May 22, 2006 6:24 PM
To: Loo, Peter # PHX
Cc: dbi-users@perl.org
Subject: Re: Where are the selected rows stored using fetchall_arrayref

There is nothing to be gained here by using fetchall_arrayref, and you
will almost certainly have problems. You want to actually do:

while ($row =3D $sth->fetchrow_arrayref()) {
$sock->send(join($delim, @$row));
}

Mark

Loo, Peter # PHX wrote:
> Hi,
> =20
> I was wondering if someone can tell me how, where and what Perl DBI=20
> stores the data returned from fetchall_arrayref. For example: if I=20
> issues a SELECT statement such as "select a.col1, b.col2, c.col3 from
> someTable1 a, someTable2 b, someTable3 c where someCondition". From=20
> this I get an array reference. I understand that the array references

> are pointers, but where are these pointers stored.
> =20
> I will be using Perl DBI to dump approximately five billion rows=20
> through a socket to another server where another program will bulk=20
> load to a database server.
> =20
> Here is an example of what I am going to be doing:
> =20
> =20
> unless ($arrayref =3D $sth->fetchall_arrayref()) {
> $MESSAGE =3D "sub_fetchallArrayref() - $DBI::errstr";
> $STATUS =3D $FAILURE;
> sub_exit();
> }
> =20
> my $sock =3D new IO::Socket::INET->new(PeerPort =3D> $port, Proto =
=3D>=20
> $proto, PeerAddr =3D> $node);
> if (!$sock) {
> print STDERR "Unable to open socket to $node at port $port using

> protocol $proto.\n";
> return $FAILURE;
> }
> =20
> foreach $row (@{$arrayref}) {
> $piped =3D join($delim, @$row);
> $sock->send($piped) || return $FAILURE;
> }
> =20
> My concern is I might run out of memory. Hope someone can shed some=20
> light.
> =20
> Thanks in advance.
> =20
> Peter
>=20
>=20
> This E-mail message is for the sole use of the intended recipient(s)
and may contain confidential and privileged information. Any
unauthorized review, use, disclosure or distribution is prohibited. If
you are not the intended recipient, please contact the sender by reply
E-mail, and destroy all copies of the original message.
>=20


This E-mail message is for the sole use of the intended recipient(s) and
may contain confidential and privileged information. Any unauthorized
review, use, disclosure or distribution is prohibited. If you are not
the intended recipient, please contact the sender by reply E-mail, and
destroy all copies of the original message.


This E-mail message is for the sole use of the intended recipient(s) and =
may contain confidential and privileged information. Any unauthorized =
review, use, disclosure or distribution is prohibited. If you are not =
the intended recipient, please contact the sender by reply E-mail, and =
destroy all copies of the original message.