Beefy Boxes and Bandwidth Generously Provided by pair Networks
Clear questions and runnable code
get the best and fastest answer
 
PerlMonks  

Creating SELECT-like look-up tables as hashes

by loris (Hermit)
on Nov 29, 2013 at 14:14 UTC ( #1064955=perlquestion: print w/ replies, xml ) Need Help??
loris has asked for the wisdom of the Perl Monks concerning the following question:

Hi Monasterians,

I have data from a database which I have read into an array of hashrefs. I want to perform SELECT-like operations on the data. For this I am creating hash-based look-up tables with combinations of columns in the following manner:

my @rows = ({id=>0,fur=>'t',legs=>'2',tail=>'y'}, {id=>1,fur=>'t',legs=>'2',tail=>'y'}, {id=>2,fur=>'t',legs=>'2',tail=>'y'}, {id=>3,fur=>'f',legs=>'4',tail=>'y'}, {id=>4,fur=>'f',legs=>'4',tail=>'y'}, ); my %lookup_id = map { $_->{id}, $_ } @rows; my %lookup_fur_and_legs; while (my ($id,$ref) = each %lookup_id) { my $key = $ref->{fur} . $ref->{legs}; if (exists $lookup_fur_and_legs{$key}) { push @{$lookup_fur_and_legs{$key}}, $ref; } else { $lookup_fur_and_legs{$key} = [$ref]; } }

Is there a better way of creating such pseudo-SELECT look-up hashes?

Cheers,

Loris

Comment on Creating SELECT-like look-up tables as hashes
Download Code
Re: Creating SELECT-like look-up tables as hashes
by choroba (Abbot) on Nov 29, 2013 at 14:23 UTC
    No need to check for existence, Perl has autovivification:
    push @{ $lookup_fur_and_legs{$key} }, $ref;

    It will create the new array ref if needed.

    Update:Also, as $id is not used, you can change the loop to

    for my $ref (values %lookup_id) {
    لսႽ ᥲᥒ⚪⟊Ⴙᘓᖇ Ꮅᘓᖇ⎱ Ⴙᥲ𝇋ƙᘓᖇ
Re: Creating SELECT-like look-up tables as hashes
by BrowserUk (Pope) on Nov 29, 2013 at 15:21 UTC
    I have data from a database which I have read into an array of hashrefs. I want to perform SELECT-like operations on the data.

    If the data is already in a database, why would you read all the data from the db into Perl and then try to recreate the basic db SELECT operation -- for which the DB is designed and highly optimised -- using Perl?


    With the rise and rise of 'Social' network sites: 'Computers are making people easier to use everyday'
    Examine what is said, not who speaks -- Silence betokens consent -- Love the truth but pardon error.
    "Science is about questioning the status quo. Questioning authority".
    In the absence of evidence, opinion is indistinguishable from prejudice.

      I am not actually reading all the data into Perl, just the smaller of two tables in an SQLite DB (one has 40000 records, the other 26 million). Profiling with Devel::NYTProf indicated that around 50% of the time was spent doing SELECTs on the database, so that looked like a good starting point for optimisation. As I have access to a cluster with available memory varying between 18-90 GB per node, I thought it might be worthwhile investigating whether some of the operations could be performed in memory, especially as I currently only require two such look-up tables.

      The idea is that with the look-up tables I can create them once and thereby effectively store the results of multiple SELECTS, which can then be accessed via the hash key. This seemed like it might be faster in Perl, but as you point out, a single SELECT is what the DB is optimised to do. So I guess my mileage may vary.

      Cheers,

      loris

        I'd suggest creating temporary copies of one or both of your tables in an SQLite In-memory DB.

        If you really need more performance than that gives you; and if your select queries are sufficiently predictable that you could pre-index the smaller table, that might be worth thinking about.


        With the rise and rise of 'Social' network sites: 'Computers are making people easier to use everyday'
        Examine what is said, not who speaks -- Silence betokens consent -- Love the truth but pardon error.
        "Science is about questioning the status quo. Questioning authority".
        In the absence of evidence, opinion is indistinguishable from prejudice.
Re: Creating SELECT-like look-up tables as hashes
by NetWallah (Abbot) on Nov 29, 2013 at 22:39 UTC
    Since you are looking-up by fur and lets, build a hash with the keys and values you need, instead of by ID:
    ## Instead of ## my %lookup_id = map { $_->{id}, $_ } @rows; my %lookup_fur_and_legs; push (@{ $lookup_fur_and_legs { $_->{fur} . $_->{legs} } }, $_ ) for +@rows; # This now allows, given any fur-legs combo : $fl, for my $row (@{ $lookup_fur_and_legs{$fl} } ){ # WHatever you want with each row for thes $fl ... }
    But, as BUK indicated, filtering is better done by SQL against a database.

                 When in doubt, mumble; when in trouble, delegate; when in charge, ponder. -- James H. Boren

Re: Creating SELECT-like look-up tables as hashes
by bigdogs (Acolyte) on Dec 01, 2013 at 16:53 UTC

    In this case, the %lookup_id hash is unnecessary: you never have to find a row by id, but just to scan them all.

    A simple foreach is enough:

    foreach my $ref (@rows) { push @{ $lookup_fur_and_legs{$ref->{id}} }, $ref; }
Re: Creating SELECT-like look-up tables as hashes
by Anonymous Monk on Dec 02, 2013 at 14:37 UTC
    You are not gaining anything by reading a bunch of data into an in-memory data structure and then writing hard-coded logic (in Perl or in any other language) to do what the database is specifically designed to do. Let your program concentrate on the what of what you want, and the database will take care of how to serve-up to you only those records that are required.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://1064955]
Front-paged by Corion
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others browsing the Monastery: (7)
As of 2014-12-26 20:00 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    Is guessing a good strategy for surviving in the IT business?





    Results (175 votes), past polls