[Chicago-talk] core dump using DBI

[Michael Potter]

yea, your calculation is about right.

What I do not show in my sample code is that I do a
@aa = ();
@ab = ();
before the foreach loop.

Performance is fine: 6000 of the 27000 records are processed in a very
acceptable time.

Only the 20*2 records that are being compared need to be in memory at
one time, so memory should not be an issue either.  (apparently it is
tho ;).

As far as refactoring, I started out trying to write one multi-table
select but ran into issues with column names being identical between
the tables.  I am sure I could overcome that with more syntax in the
select, but decided the SELECT * in a foreach was less error prone.

-- 
Michael Potter

On Mon, Jul 14, 2008 at 9:52 PM, Brian Katzung <briank at kappacs.com> wrote:
> Hi Michael.
>
> If I'm following you correctly, you're talking about 1,080,000 queries and
> who knows how many results row fetches.
>
> I would seriously try to take a step back and see if you can refactor. With
> that amount of iteration and number of database requests, would it make more
> sense to make a single linear pass through all the rows of all the relevant
> tables and build your local (in memory) data structures in parallel?
>
>  - Brian
>
> Michael Potter wrote:
>>
>> Monks,
>>
>> I am core dumping in this fragment of code:
>> ----------------------------------------------
>>        foreach my $TableName (@TableNames)
>>         {
>>            my $SelectSub = qq{
>>               SELECT * FROM
>>                     $TableName
>>                  WHERE ARTKEY1 = ?
>>            };
>>
>>            my $sth_ssa = $DbHandle->prepare($SelectSub);
>>            $sth_ssa->execute($artkeyA);
>>            $Counter = 0;
>>            while (my $hsa = $sth_ssa->fetchrow_hashref())
>>            {
>>               $Counter++;
>>               delete $hsa->{ARTKEY1};
>>               $hsa->{TABLENAME} = $TableName;
>>               push @aa, $hsa;
>>            }
>>            $sth_ssa->finish();
>>
>>            my $sth_ssb = $DbHandle->prepare($SelectSub);
>>            $sth_ssb->execute($artkeyB);
>>            $Counter = 0;
>>            while (my $hsb = $sth_ssb->fetchrow_hashref())
>>            {
>>               $Counter++;
>>               delete $hsb->{ARTKEY1};
>>               $hsb->{TABLENAME} = $TableName;
>>               push @ab, $hsb;
>>            }
>>            $sth_ssb->finish();
>>         }
>> -------------------------------------
>>
>> I pulled up the core dump in the debugger and it is failing with this
>> error:
>> malloc_common.extend_brk(internal error: assertion failed at line 3626
>> in file frame.c
>>
>> There is another loop that surrounds this loop and runs about 27,000
>> times.  The loop you see runs about 20 times for each of the 27,000.
>> It fails at about 6200.  I shifted the data to confirm that the actual
>> data is not the problem.
>>
>> I am on AIX/DB2/Perl 5.8.5.
>>
>> I know it would be ideal if I could create a sample application that
>> would reproduce this problem, but with all the data required to
>> reproduce the problem this is not practical.  I also suspect the
>> problem is in DBI and way over my head to debug.
>>
>> My next action will be to move this loop to it's own script so that
>> memory will be cleaned up by the termination of the script, then I
>> will execute that script 27,000 times.
>>
>> Any better ideas?
>>
>
> --
> Brian Katzung, Kappa Computer Solutions, LLC
> Leveraging UNIX, GNU/Linux, open source, and custom
> software solutions for business and beyond
> Phone: 877.367.8837 x1  http://www.kappacs.com
>
> _______________________________________________
> Chicago-talk mailing list
> Chicago-talk at pm.org
> http://mail.pm.org/mailman/listinfo/chicago-talk
>