Hi my dear Monks.
I wrote part of provisioning interface which manage data from inventory management into performance managent. Data are stored into SQL, Oracle database. In predefined time my script compare SQL table with its SQL local backup copy to find out changes... So I am comparing two SQL tables with identical structure to find:

1. records existing only in first table
2. records existing only in second table
3. records with identical primary keys, but with different content.
4. record which are totaly identicals.

To do that I am using DBI::Oracle module and ->selectall_hashref() method to fetch whole content of both tables to two hash data structures. Than I have simple procedure to compare both hashes and as result I get three hashes (see points 1-3, I don't take care about identical record - point 4) This works good for now. I am using something like this:

foreach $key ( keys %{$hash1} ) {
    if ( exists( $hash2->{$key} ) ) {
        if ( not compare( $hash1->{$key}, $hash2->{$key} ) ) {
            $hash3->{$key} = $hash1->{$key};
        }
        delete( $hash1->{$key} );
        delete( $hash2->{$key} );
    }
}

[download]

where $hash1 and $hash2 are references to input tables and output are hash1(see point 1.), hash2(see point 2.) and hash3(see point 3.)

Problem is, that in this time tables contains about hundreds of records. Now we are going to store about 10.000 records in tables!!! Because I am reading whole table into perl variable (into the memory):

1. I want to know, if perl can handle so big data structure or is this just HW sizing problem?
2. Is there more efficient way to do this comparision in perl?
3. Is there more efficient way how to compare two tables with same results directly in SQL?

->selectall_hashref() method return hash of arrays, where each array is one row in SQL table and hash key is in my example sql primary key. Table has about 20 collumns.

Hope that the code I wrote bellow is good. I tried to memorized it, but my brain work worse than sql database :-). Thank you for help brothers.

Regards LTOW

Li Tin O've Weedle
mad Tsort's philosopher

In reply to SQL vs Perl table comparing by LiTinOveWeedle

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.