For loop suggestions

[sure_mp]

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.

[moritz]

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.

[sure_mp]

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

[moritz]

Just sort before you loop.

[choroba]

You can even format and indent the code you posted before. Please, do not hestitate to do so.

[NetWallah]

Try Range::Object::Serial - it can store ranges compactly, and provides methods to check for membership.

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

[space_monk]

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.

[roboticus]

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";

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";

...roboticus

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