[ic] Identify cause of slowness loading returns.html
DB
db at m-and-d.com
Mon Apr 25 19:50:46 UTC 2011
When I use the Foundation catalog's returns.html page, it takes an
annoyingly long time to load. I'm trying to find a way to speed this up.
My transactions and userdb tables have grown somewhat large.
The SQL query is:
[query st=db arrayref=orders
sql="SELECT
code,status,nitems,subtotal,shipping,handling,total_cost,payment_method,order_date
FROM transactions
WHERE username = '[data base=session field=username filter=sql]'
ORDER by code
"][/query]
I try that query directly in mysql for a specific username and it is
very fast. The complete code from the page is below for reference. Can
someone suggest what might be causing the delay, and if you're feeling
generous, suggest a cure?
DB
================BEGIN CODE================
[query st=db arrayref=orders
sql="SELECT
code,status,nitems,subtotal,shipping,handling,total_cost,payment_method,order_date
FROM transactions
WHERE username = '[data base=session field=username filter=sql]'
[comment] DB 04_2011 to speed things up[/comment]
AND order_number > 178000
[comment] DB 04_2011 to speed things up[/comment]
ORDER by code
"][/query]
[mvasp tables=transactions]
<%
my $uid = $Session->{username};
if (! $uid) {
$Document->write (qq{<H3><font __FFACE__>You are not logged
in.</font></H3>});
return;
}
my $orders = $Tmp->{orders};
if(! $orders or scalar @$orders == 0) {
$Document->write( "<p>No orders found for $uid.</p>");
return;
}
HTML <<'EOF';
Please select an order below to continue with the return process.
</p>
<FORM ACTION="[process-target]" METHOD=POST>
<input type=hidden name=mv_session_id value="[data session id]">
<INPUT TYPE=hidden NAME=mv_todo VALUE=return>
<INPUT TYPE=hidden NAME=mv_nextpage VALUE="returns_processed">
<TABLE CELLSPACING=0 CELLMARGIN=0 BORDER=1 WIDTH=450>
EOF
my $header_template = <<'EOF';
<TR>
<TD VALIGN=bottom>
ORDER ID
</TD>
<TD VALIGN=bottom>
DATE
</TD>
<TD VALIGN=bottom>
TOTAL
</TD>
<TD VALIGN=bottom>
STATUS
</TD>
</TR>
EOF
my $line_template = <<'EOF';
<TR">
<TD VALIGN=TOP align="center">
<A HREF="$detail_url">$order_number</A>
</TD>
<TD VALIGN=TOP>
$order_date
</TD>
<TD ALIGN=right VALIGN=TOP>
$total_cost
</TD>
<TD VALIGN=TOP align="right">
$status
</TD>
</TR>
EOF
my %hash;
my @fields = qw/order_number status nitems subtotal shipping
handling total_cost payment_method order_date/;
my $row;
my %summary;
my $first;
my $record;
HTML $header_template;
foreach $record (@$orders) {
my $line = $line_template;
@hash{@fields} = @$record;
$hash{detail_url} = $Tag->area( {
href => 'query/order_return',
arg => $hash{order_number},
});
if($hash{status} =~ /\d/) {
my @ids = grep /\S/, split /\s+/, $hash{status};
$hash{status} = '';
for(@ids) {
$hash{status} .= <<EOF;
<A
HREF="http://wwwapps.ups.com/etracking/tracking.cgi?InquiryNumber1=$_&TypeOfInquiryNumber=T">UPS
$_</A>
EOF
}
}
else {
$hash{status} = <<EOF;
$hash{status}
EOF
}
for(qw/subtotal shipping total_cost/) {
$hash{$_} = $Tag->currency ({ body=> $hash{$_} });
}
if($hash{handling}) {
$hash{handling} = "<BR>(handling " .
$Tag->currency ({ body=> $hash{handling} }) .
")";
}
else {
$hash{handling} = '';
}
$line =~ s/\$(\w+)/$hash{$1}/g;
HTML $line;
}
HTML "</TABLE></FORM>";
%>
[/mvasp]
<BR CLEAR=ALL>
[seti export][tag export transactions transactions.txt][/tag][/seti]
[seti export][tag export orderline orderline.txt][/tag][/seti]
================END CODE================
More information about the interchange-users
mailing list