[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