Beefy Boxes and Bandwidth Generously Provided by pair Networks
Welcome to the Monastery

Re: Re-indexing a SQL resultset by id

by sundialsvc4 (Abbot)
on Jul 08, 2011 at 22:17 UTC ( #913434=note: print w/replies, xml ) Need Help??

in reply to Re-indexing a SQL resultset by id

If you could arrange things so that the query specifies ORDER BY id, then your logic here would become very simple.   Every occurrence of any given id value would become, “by definition, adjacent,” and your logic could simply remember what was the value from the preceding row and notice if the value had changed.

Perl’s “auto-vivification” features can come in very handy here.   If we have a hashref $foo which is to contain an arrayref of values found for each key, we can write very “streamlined” logic such as the following:

while (my $row = $query->fetchrow_hashref) { my $key = $$row{'key'}; # PEDANTIC FOR CLARITY ... my $val = $$row{'value'}; push @{ $$foo{$key} }, $val; }
(caution... extemporaneous code ... Your Mileage May Vary™)

The “clever time-saving goodness” here being that, if $foo does not yet contain a hash-bucket for a given $key, then ... lo! ... magically, it does!   And, it is understood to be an (empty) arrayref!   And, the desired value magically gets pushed onto it!!   (How convenient can you get?)

(In the example above,   $$foo{'bletch'}   is simply a shorthand for:   $foo->{'bletch'}  .)

If you combine this trick with the previously mentioned ORDER BY, the process actually becomes efficient, because the $key values will be arriving in an entirely predictable sequence.   Page-faults will therefore be minimized.   Even (especially...!) if you have millions of rows in your SQL table, this will make a very noticeable difference in speed.

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://913434]
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others surveying the Monastery: (5)
As of 2017-11-23 05:52 GMT
Find Nodes?
    Voting Booth?
    In order to be able to say "I know Perl", you must have:

    Results (328 votes). Check out past polls.