Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl: the Markov chain saw
 
PerlMonks  

Re: Re-indexing a SQL resultset by id

by sundialsvc4 (Monsignor)
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.


Comment on Re: Re-indexing a SQL resultset by id
Download Code

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://913434]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others wandering the Monastery: (11)
As of 2014-07-30 18:39 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    My favorite superfluous repetitious redundant duplicative phrase is:









    Results (239 votes), past polls