Optimizing Loop

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

The following code is called pretty frequently in an application I'm working on. After executing two MySQL queries it loops through the result sets and updates a variable for each record that contains the available quantity of a given inventory item. I'm trying to figure out if there is a faster way to do what the following code does. @inventory_items ends up with around 2200 items, and the $stock_change_sth returns around 325 rows.

    my @inventory_items_array;
    my $inventory_item;
    my %stock_current;
    my %stock_minimum;
    while ($inventory_item = $sth->fetchrow_hashref()){
        
        $stock_current{$inventory_item->{'inventory_item_id'}} = $inve
+ntory_item->{'starting_quant'};
        $stock_minimum{$inventory_item->{'inventory_item_id'}} = $inve
+ntory_item->{'starting_quant'};
        $inventory_item->{'search'} = join(',', $inventory_item->{'nam
+e'}, $inventory_item->{'description'}, $inventory_item->{'inventory_i
+tem_id'});
        
        push(@inventory_items_array, $inventory_item);
    }
    
    my $stock_change_data_ref;
    while ($stock_change_data_ref = $stock_change_sth->fetchrow_hashre
+f()){
        $stock_current{$stock_change_data_ref->{'inventory_item_id'}} 
++= $stock_change_data_ref->{'Qty_Change'};
        if ($stock_current{$stock_change_data_ref->{'inventory_item_id
+'}} < $stock_minimum{$stock_change_data_ref->{'inventory_item_id'}}){
            $stock_minimum{$stock_change_data_ref->{'inventory_item_id
+'}} = $stock_current{$stock_change_data_ref->{'inventory_item_id'}};
        }
    }
    
    foreach(@inventory_items_array){
        $_->{'rem_avail_quant'} = $stock_minimum{$_->{'inventory_item_
+id'}};
        $_->{'available'} = $_->{'rem_avail_quant'} . '/' . $_->{'inv_
+quant'};
    }
[download]

Comment on Optimizing Loop Download Code

Replies are listed 'Best First'.
Re: Optimizing Loop by davido (Cardinal) on Feb 17, 2014 at 23:27 UTC
Familiarize yourself with two important tools. First, Devel::NYTProf for profiling your application to determine where it is spending the most time. Second, the MySQL Slow Query Log. If it turns out you're spending a lot of time in the database, check out the book High Performance MySQL, from O'Reilly. There's a lot of good insight therein. But really, first profile. Then optimize. Dave	[reply]
Re: Optimizing Loop by LanX (Saint) on Feb 17, 2014 at 23:14 UTC
You better start by timing where the bottlenecks are, we can't tell from a distance how your system performs. some ideas: try reading repeated entries (like the IDs) just once from the hash into a $scalar and reuse them measure if reading the whole table into a AoH at once instead of fetching every single row makes a difference consider fetching arrays instead of hashes consider SQL-queries/stored procedures which return the data directly in desired format > to do what the following code does. documenting / explaining what it "does" might facilitate getting help ... Cheers Rolf ( addicted to the Perl Programming Language)	[reply]
Re^2: Optimizing Loop by Anonymous Monk on Feb 18, 2014 at 10:34 UTC
The last two points are really important. The final point doubly so. (But you need to learn basic SQL for that.) (I'll also add that bound columns are even more efficient than arrays: `$sth->bind_columns(\$col1, \$col2, \$col3); while ($sth->fetch) { print "$col1\n" }`)	[reply] [d/l]
Re: Optimizing Loop by bulk88 (Priest) on Feb 18, 2014 at 05:26 UTC
`$stock_current{$inventory_item->{'inventory_item_id'}} = $inve +ntory_item->{'starting_quant'}; $stock_minimum{$inventory_item->{'inventory_item_id'}} = $inve +ntory_item->{'starting_quant'};` [download] Put $inventory_item->{'inventory_item_id'} and $inventory_item->{'starting_quant'} in a lexical scalar ref or copy the string to a lexical. Hash lookups are expensive and multiple lookups can not optimized away (what if its a tied hash where every fetch changed the key's value?). `$stock_current{$stock_change_data_ref->{'inventory_item_id'}} ++= $stock_change_data_ref->{'Qty_Change'}; if ($stock_current{$stock_change_data_ref->{'inventory_item_id +'}} < $stock_minimum{$stock_change_data_ref->{'inventory_item_id'}}){ $stock_minimum{$stock_change_data_ref->{'inventory_item_id +'}} = $stock_current{$stock_change_data_ref->{'inventory_item_id'}}; }` [download] Stop fetching $stock_change_data_ref->{'inventory_item_id'} 5 times. See above notes. Also note there searching and sorting algorithms that may help you. A CS nerd can help you with those more than me. `foreach(@inventory_items_array){ $_->{'rem_avail_quant'} = $stock_minimum{$_->{'inventory_item_ +id'}}; $_->{'available'} = $_->{'rem_avail_quant'} . '/' . $_->{'inv_ +quant'};` [download] It looks to me like this loop can be merged with the "while ($stock_change_data_ref = $stock_change_sth->fetchrow_hashre f()){" but I am not sure.	[reply] [d/l] [select]