Hi Srinivas,<br> You will need to profile your code (check Devel::NYTProf on CPAN) to find out the exact bottleneck, <br><br>you can do a grep from xls1 once instead of going through all the values each time <br><br>
my @xls1_vals = grep {$_->{'row2'} > 32 && $_->{'col2'} > 0} @xls1;<br><br>for my $row ( grep {$_->{'row2'} > 32 && $_->{'col2'} > 0} @xls2 )<br>{<br>
<br>}<br><br><br>Its easier to actually find out where the issue is first<br><br><br>-Kiran<br><br><br><div class="gmail_quote">2009/6/15 Srinivas Reddy <span dir="ltr"><<a href="mailto:SReddy@securities.com">SReddy@securities.com</a>></span><br>
<blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
<div link="blue" vlink="purple" lang="EN-US">
<div>
<p><font size="2" face="Arial"><span style="font-size: 10pt; font-family: Arial;">Hi , </span></font></p>
<p><font size="2" face="Arial"><span style="font-size: 10pt; font-family: Arial;"> </span></font></p>
<p><font size="2" face="Arial"><span style="font-size: 10pt; font-family: Arial;">Please help to optimize the code below. We basically read 2
xls files into an array @xls2 and @xls1 . For each cell we want to compare (
starting from col 32 ) values in each cell and if they are not equal we need to
store it in a different array. This we will then use to bold those particular cells.
</span></font></p>
<p><font size="2" face="Arial"><span style="font-size: 10pt; font-family: Arial;"> </span></font></p>
<p><font size="2" face="Arial"><span style="font-size: 10pt; font-family: Arial;">The loop which does this comparison could be replacement by
some in built array difference kind of function. I am new to perl so apologies if
I ve used any wrong terminologies here. Appreciate your help on the same.
Please find attached whole code as well for reference </span></font></p>
<p><font size="2" face="Arial"><span style="font-size: 10pt; font-family: Arial;"> </span></font></p>
<p><font size="2" face="Arial"><span style="font-size: 10pt; font-family: Arial;">sub cell_handler2 {</span></font></p>
<p><font size="2" face="Arial"><span style="font-size: 10pt; font-family: Arial;"> </span></font></p>
<p><font size="2" face="Arial"><span style="font-size: 10pt; font-family: Arial;"> my $workbook = $_[0];</span></font></p>
<p><font size="2" face="Arial"><span style="font-size: 10pt; font-family: Arial;"> my $sheet_index = $_[1];</span></font></p>
<p><font size="2" face="Arial"><span style="font-size: 10pt; font-family: Arial;"> my $row = $_[2];</span></font></p>
<p><font size="2" face="Arial"><span style="font-size: 10pt; font-family: Arial;"> my $col = $_[3];</span></font></p>
<p><font size="2" face="Arial"><span style="font-size: 10pt; font-family: Arial;"> my $cell = $_[4];</span></font></p>
<p><font size="2" face="Arial"><span style="font-size: 10pt; font-family: Arial;"> # Construct array for Dx code</span></font></p>
<p><font size="2" face="Arial"><span style="font-size: 10pt; font-family: Arial;"> </span></font></p>
<p><font size="2" face="Arial"><span style="font-size: 10pt; font-family: Arial;"> push @xls2 , {</span></font></p>
<p><font size="2" face="Arial"><span style="font-size: 10pt; font-family: Arial;"> 'row2' => $row,</span></font></p>
<p><font size="2" face="Arial"><span style="font-size: 10pt; font-family: Arial;"> 'col2' => $col,</span></font></p>
<p><font size="2" face="Arial"><span style="font-size: 10pt; font-family: Arial;"> 'value2' => $cell->value(),</span></font></p>
<p><font size="2" face="Arial"><span style="font-size: 10pt; font-family: Arial;"> };</span></font></p>
<p><font size="2" face="Arial"><span style="font-size: 10pt; font-family: Arial;"> return @xls2;</span></font></p>
<p><font size="2" face="Arial"><span style="font-size: 10pt; font-family: Arial;"> </span></font></p>
<p><font size="2" face="Arial"><span style="font-size: 10pt; font-family: Arial;">}</span></font></p>
<p><font size="2" face="Arial"><span style="font-size: 10pt; font-family: Arial;">#print Dumper(@xls2);</span></font></p>
<p><font size="2" face="Arial"><span style="font-size: 10pt; font-family: Arial;"># Print the End Time to read second excel file</span></font></p>
<p><font size="2" face="Arial"><span style="font-size: 10pt; font-family: Arial;">print scalar(localtime) . ": End Reading Excel2
\n";</span></font></p>
<p><font size="2" face="Arial"><span style="font-size: 10pt; font-family: Arial;"> </span></font></p>
<p><font size="2" face="Arial"><span style="font-size: 10pt; font-family: Arial;">print scalar(localtime) . ": Start Populating Bolding
Array \n";</span></font></p>
<p><font size="2" face="Arial"><span style="font-size: 10pt; font-family: Arial;"># Inefficient way of looping</span></font></p>
<p><font size="2" face="Arial"><span style="font-size: 10pt; font-family: Arial;"># Please change this as it could boost the performance
significantly </span></font></p>
<p><font size="2" face="Arial"><span style="font-size: 10pt; font-family: Arial;">for(@xls2) {</span></font></p>
<p><font size="2" face="Arial"><span style="font-size: 10pt; font-family: Arial;"> if($_->{'col2'} > 32 &&
$_->{'row2'} > '0' ) {</span></font></p>
<p><font size="2" face="Arial"><span style="font-size: 10pt; font-family: Arial;"> $newvalue =
$_->{'value2'};</span></font></p>
<p><font size="2" face="Arial"><span style="font-size: 10pt; font-family: Arial;"> $newrow =
$_->{'row2'};</span></font></p>
<p><font size="2" face="Arial"><span style="font-size: 10pt; font-family: Arial;"> $newcol =
$_->{'col2'};</span></font></p>
<p><font size="2" face="Arial"><span style="font-size: 10pt; font-family: Arial;"> for(@xls1) {</span></font></p>
<p><font size="2" face="Arial"><span style="font-size: 10pt; font-family: Arial;"> $oldrow =
$_->{'row1'};</span></font></p>
<p><font size="2" face="Arial"><span style="font-size: 10pt; font-family: Arial;"> $oldcol =
$_->{'col1'}; </span></font></p>
<p><font size="2" face="Arial"><span style="font-size: 10pt; font-family: Arial;"> if($_->{'col1'}
> 32 && $_->{'row1'} > 0 && $_->{'row1'} eq $newrow
&& $_->{'col1'} eq $newcol) {</span></font></p>
<p><font size="2" face="Arial"><span style="font-size: 10pt; font-family: Arial;"> $oldvalue
= $_->{'value1'};</span></font></p>
<p><font size="2" face="Arial"><span style="font-size: 10pt; font-family: Arial;"> last;</span></font></p>
<p><font size="2" face="Arial"><span style="font-size: 10pt; font-family: Arial;"> }</span></font></p>
<p><font size="2" face="Arial"><span style="font-size: 10pt; font-family: Arial;"> }</span></font></p>
<p><font size="2" face="Arial"><span style="font-size: 10pt; font-family: Arial;"> if($oldvalue ne
$newvalue) {</span></font></p>
<p><font size="2" face="Arial"><span style="font-size: 10pt; font-family: Arial;"> push
@bold_array , {</span></font></p>
<p><font size="2" face="Arial"><span style="font-size: 10pt; font-family: Arial;"> 'row'
=> $_->{'row2'},</span></font></p>
<p><font size="2" face="Arial"><span style="font-size: 10pt; font-family: Arial;"> 'col'
=> $_->{'col2'},</span></font></p>
<p><font size="2" face="Arial"><span style="font-size: 10pt; font-family: Arial;"> 'value'
=> $_->{'value2'},</span></font></p>
<p><font size="2" face="Arial"><span style="font-size: 10pt; font-family: Arial;"> };</span></font></p>
<p><font size="2" face="Arial"><span style="font-size: 10pt; font-family: Arial;"> }</span></font></p>
<p><font size="2" face="Arial"><span style="font-size: 10pt; font-family: Arial;"> }</span></font></p>
<p><font size="2" face="Arial"><span style="font-size: 10pt; font-family: Arial;">}</span></font></p>
<p><font size="2" face="Arial"><span style="font-size: 10pt; font-family: Arial;"> </span></font></p>
<p><font size="2" face="Arial"><span style="font-size: 10pt; font-family: Arial;"> </span></font></p>
<p><font size="2" face="Arial"><span style="font-size: 10pt; font-family: Arial;">Regards,</span></font></p>
<p><font size="2" face="Arial"><span style="font-size: 10pt; font-family: Arial;"> </span></font></p>
<p><font size="2" face="Arial"><span style="font-size: 10pt; font-family: Arial;">Srinivas Reddy</span></font></p>
</div>
<br>
______________________________________________________________________<br>
This communication contains information which is confidential. It is for the<br>
exclusive use of the intended recipient(s). If you are not the intended<br>
recipient(s) please note any distribution, copying or use of this<br>
communication or the information in it is strictly prohibited. If you have<br>
received this communication in error please notify us by e-mail or<br>
by telephone (as above) and then delete the e-mail and all attachments and<br>
any copies thereof.<br>
______________________________________________________________________<br>
</div>
<br>_______________________________________________<br>
Bangalore-pm mailing list<br>
<a href="mailto:Bangalore-pm@pm.org">Bangalore-pm@pm.org</a><br>
<a href="http://mail.pm.org/mailman/listinfo/bangalore-pm" target="_blank">http://mail.pm.org/mailman/listinfo/bangalore-pm</a><br></blockquote></div><br>