RE: Where are the selected rows stored using fetchall_arrayref
- From: stbaldwin@xxxxxxxxxxxxxxxx (Steve Baldwin)
- Date: Tue, 23 May 2006 09:41:37 +1000
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@xxxxxxxxxxxxxxxxxxxxxxxx]
Sent: Tuesday, 23 May 2006 9:35 AM
To: dbi-users@xxxxxxxx
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.
.
- References:
- Where are the selected rows stored using fetchall_arrayref
- From: Peter # PHX Loo
- Where are the selected rows stored using fetchall_arrayref
- Prev by Date: Where are the selected rows stored using fetchall_arrayref
- Next by Date: Re: Where are the selected rows stored using fetchall_arrayref
- Previous by thread: Where are the selected rows stored using fetchall_arrayref
- Next by thread: Re: Where are the selected rows stored using fetchall_arrayref
- Index(es):
Relevant Pages
|
