Creating SELECT-like look-up tables as hashes

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];
  }
}
[download]

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

Replies are listed 'Best First'.
Re: Creating SELECT-like look-up tables as hashes by BrowserUk (Patriarch) 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.	[reply]
Re^2: Creating SELECT-like look-up tables as hashes by loris (Hermit) on Dec 02, 2013 at 16:34 UTC
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	[reply]
Re^3: Creating SELECT-like look-up tables as hashes by BrowserUk (Patriarch) on Dec 02, 2013 at 16:47 UTC
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.	[reply]
Re^4: Creating SELECT-like look-up tables as hashes by loris (Hermit) on Dec 03, 2013 at 09:47 UTC
Re^5: Creating SELECT-like look-up tables as hashes by BrowserUk (Patriarch) on Dec 03, 2013 at 16:07 UTC
Some notes below your chosen depth have not been shown here
Re: Creating SELECT-like look-up tables as hashes by choroba (Cardinal) on Nov 29, 2013 at 14:23 UTC
No need to check for existence, Perl has autovivification: `push @{ $lookup_fur_and_legs{$key} }, $ref;` [download] 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) {` [download] لսႽ† ᥲᥒ⚪⟊Ⴙᘓᖇ Ꮅᘓᖇ⎱ Ⴙᥲ𝇋ƙᘓᖇ	[reply] [d/l] [select]
Re: Creating SELECT-like look-up tables as hashes by NetWallah (Canon) 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 ... }` [download] 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	[reply] [d/l]
Re: Creating SELECT-like look-up tables as hashes by bigdogs (Sexton) 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; }` [download]	[reply] [d/l] [select]
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.	[reply]


Think about Loose Coupling
	PerlMonks