Abigail

Actually, the source database is a wierd beast called Versant. It's rather an object persistence store than a database, and it is non-relational.

When it comes to locking strategies, Versant tends to assume that you know what you are doing (something which was not the case in this instance).

Why not have the one database dump its table using its native tools?

That's a thought. The client where I am working do tend to have a philosophy of doing everything in application code (C++, yeugh). I looked at

db2tty -D $DBAME -i myClass
[download]

and this does dump all objects in myClass to stdout. There are issues with escaping and format conversion, and it would be hard to convince my colleagues to abandon the extractor code they are already using.

Anyone else out there using Versant?

--
I'm Not Just Another Perl Hacker

I am aware of a similar issue with DBI code. In particular, the use of selectall or fetchall, compared with fetchrow. I have seen instances.

Depending on your DBD, calling fetchrow() may also slurp the entire result into memory. It just won't give it to you all at once, which might reduce memory usage to some degree in exchange for the added overhead in repeated fetchrow() calls.

If you're serious about avoiding this problem in DBI then you need to use LIMIT and OFFSET (or their cursorly equivalent) to step through your result set.

-sam

Depending on your DBD, calling fetchrow() may also slurp the entire result into memory. It just won't give it to you all at once, which might reduce memory usage to some degree in exchange for the added overhead in repeated fetchrow() calls.

I would contend that this is a bug, and a pretty serious one at that. While pre-fetching data is (often) a good idea, it should never be done blindly without checking the available space (and pre-fetching X million rows doesn't make sense, anyway!)

Michael

Contend all you want, as far as I know it's the truth for at least DBD::mysql and DBD::Pg. I imagine it would be fairly easy to test this theory with a properly giant DB and GTop.

-sam

Slightly relating issue. Is

while (local $_= each %hash) { .... }
[download]

for (keys %hash) { .... }
[download]

for (@array) { .... }
[download]

The for version creates a (potentially large) list, whereas the while version does not.

The cost of allocating the list is offset against repeated calls to each, with the result that for small lists, for iterates faster, but on larger lists the while version wins out. A quick test show that the break even point on my system is around 8000 elements. YMMV

use Benchmark qw[ cmpthese ];

$h{ $_ } = 0 for 1 .. 100;
cmpthese( -3, { 
    for   => q[ $h{ $_ }++ for keys %h; ], 
    while => q[ $h{ $_ }++ while $_ = each %h; ] 
});

         Rate while   for
while 11937/s    --  -23%
for   15544/s   30%    --

$h{ $_ } = 0 for 1 .. 1000;
cmpthese( -3, { 
    for   => q[ $h{ $_ }++ for keys %h; ], 
    while => q[ $h{ $_ }++ while $_ = each %h; ] 
});

        Rate while   for
while 1165/s    --  -26%
for   1581/s   36%    --

$h{ $_ } = 0 for 1 .. 10000;
cmpthese( -3, { 
    for   => q[ $h{ $_ }++ for keys %h; ], 
    while => q[ $h{ $_ }++ while $_ = each %h; ] } );

        Rate   for while
for   80.0/s    --   -8%
while 86.9/s    9%    --

$h{ $_ } = 0 for 1 .. 5000;
cmpthese( -3, { 
    for   => q[ $h{ $_ }++ for keys %h; ], 
    while => q[ $h{ $_ }++ while $_ = each %h; ] 
});

       Rate while   for
while 207/s    --  -15%
for   243/s   17%    --

$h{ $_ } = 0 for 1 .. 8000;
cmpthese( -3, { 
    for   => q[ $h{ $_ }++ for keys %h; ], 
    while => q[ $h{ $_ }++ while $_ = each %h; ] 
});

       Rate   for while
for   119/s    --   -0%
while 119/s    0%    --
[download]

---

Examine what is said, not who speaks.

"Efficiency is intelligent laziness." -David Dunham
"Think for yourself!" - Abigail