sure_mp has asked for the wisdom of the Perl Monks concerning the following question:

I have a field which takes values from 0 to 6,00,000. For this field values in database are say 5,6,7,8,45,91,92,93,94. I have to get free values and used value suggestions for this field. 1.USED Minvalue= 0 maxvalue=94( from database) i use a for loop (i=minvalue;i<=maxvalue;i++) Loop runs 94 times in this case and each time it checks if something matches database entries (5,6,7,45..94). I print output ranges like. Used values: 5 to 8 45 91 to 94 Since values are less it works fast. If there was even one entry like 490000 in database then loop would have run for a very long time. 2. FREE Min value= 0 maxvalue = 600000 (from field definition) I have the same for loop concept here too so the loop runs 500000 times. And output comes after a long time. It is too time consuming. I would want output like. Free values : 0 to 4 9 to 44 46 to 90 95-600000 Any way to reduce for loop executions or use some other logic.

Replies are listed 'Best First'.
Re: For loop suggestions
by moritz (Cardinal) on Jan 11, 2013 at 06:31 UTC

    Hello sure_mp,

    your question would be much more readable if you used a dot at the end of each sentence, and occasionally a paragraph.

    As for the question: Instead looping from the minimum and the maximum value, simply loop over all the distinct values that you get from the database. For each number, check if it's 1 plus the previous number, and if yes, join them to a range.

    Perl 6 - the future is here, just unevenly distributed
[reply]
      Thanks, will indent the code properly next time. I also though about looping through the hash but aren't hashes unordered so how do i get the "last element" inside a loop
[reply]
[reply]
        You can even format and indent the code you posted before. Please, do not hestitate to do so.
        لսႽ† ᥲᥒ⚪⟊Ⴙᘓᖇ Ꮅᘓᖇ⎱ Ⴙᥲ𝇋ƙᘓᖇ
[reply]
Re: For loop suggestions
by NetWallah (Canon) on Jan 11, 2013 at 07:08 UTC
    Try Range::Object::Serial - it can store ranges compactly, and provides methods to check for membership.

    Use moritz's (++) idea above to generate ranges.

                 Most people believe that if it ain't broke, don't fix it.
            Engineers believe that if it ain't broke, it doesn't have enough features yet.

[reply]
Re: For loop suggestions
by space_monk (Chaplain) on Jan 11, 2013 at 10:48 UTC
    I'm not sure how efficient it would be, and it probably depends on what your database is, but you could perhaps find the minimum number by a single database query by joining a sequence generation and returning the lowest value of the sequence which is not a table index.
    A Monk aims to give answers to those who have none, and to learn from those who know more.
[reply]
Re: For loop suggestions
by roboticus (Chancellor) on Jan 11, 2013 at 11:45 UTC

    sure_mp:

    Ah, ignore me. I didn't read the original post closely enough, and answered the wrong question.

    If you mean SQL database when you say database, then you can ask the database directly with something like:

    ...
use DBI;
...
my $DB = DBI->connect(...);
my $ar = $DB->selectall_arrayref(<<EOSQL);
   SELECT min(colName) AS min_val, max(colName) AS max_val
   FROM   tableName
EOSQL
print "Minimum value found: $$ar[0][0]\n"
.     "Maximum value found: $$ar[0][1]\n";

    [download]

    Typically, database engines keep statistics about table columns so they can optimize queries, so the database engine many not even have to scan through the table to find all the values.

    On the other hand, if you're using a flat file, or a data structure in memory, your approach is almost good. But rather than scanning all possible values, you could just scan the records and track the minimum and maximum values:

    my ($min, $max);
for my $rec (@records) {
    $min = $$rec[$colNum] if !defined($min) or $$rec[$colNum]<$min;
    $max = $$rec[$colNum] if !defined($max) or $$rec[$colNum]>$max;
}
print "Values used are between $min and $max\n";

    [download]

    ...roboticus

    When your only tool is a hammer, all problems look like your thumb.

[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom