I have a table that is populated with the results of about 10 queries run
for 70 branches every 15 minutes for stats display purposes. These stats are
stored in stats_reports, and are grouped according to stats_groups

Here is the body of the stored procedure. It ran fine for the first month
but now it times out (Apache allows 2 minutes for the script to run). The
proc must return the lastest row for the date and time specified with a 70
min window for each report for each branch.

begin
for select orderappend from branches
where gprsenabled = 'Y'
order by orderappend
into :branchcode do
begin
for select link, grouplink, description from stats_reports
where deleted = 'N'
order by grouplink, link
into :reportlink, :grouplink, :description do
begin
result = 0;
recdate = NULL;
rectime = NULL;
select first(1) result, recdate, rectime from stats_results
where recdate = :recdatein and
(rectime >= addminute( :rectimein,-60) and rectime <
addminute( :rectimein,10)) and
branchcode = :branchcode and
reportlink = :reportlink
order by link desc
into :result, :recdate, :rectime;
suspend;
end
end
end

RECREATE TABLE STATS_RESULTS
(
LINK VARCHAR( 10) NOT NULL COLLATE NONE,
BRANCHCODE VARCHAR( 3) NOT NULL COLLATE NONE,
REPORTLINK INTEGER,
RECDATE DATE,
RECTIME TIME,
RESULT NUMERIC( 15, 2),
CONSTRAINT PK_STATS_RESULTS PRIMARY KEY (LINK)
);

CREATE ASC INDEX I_STATS_RESULTS_BRANCHCODE ON STATS_RESULTS (BRANCHCODE);
CREATE DESC INDEX I_STATS_RESULTS_LINK_SORT ON STATS_RESULTS (LINK);
CREATE ASC INDEX I_STATS_RESULTS_RECDATE ON STATS_RESULTS (RECDATE);
CREATE ASC INDEX I_STATS_RESULTS_REPORTLINK ON STATS_RESULTS (REPORTLINK);

the following plans are used

select orderappend from branches where gprsenabled = 'Y' order by
orderappend
PLAN (BRANCHES ORDER I_BRANCHES_ORDERAPPEND)

select link, grouplink, description from stats_reports where deleted = 'N'
order by grouplink, link
PLAN SORT ((STATS_REPORTS NATURAL))

select first (1) result, recdate, rectime from stats_results where recdate =
:recdatein and (rectime >= addminute (:rectimein, - 60) and rectime <
addminute (:rectimein, 10)) and branchcode = :branchcode and reportlink =
:reportlink order by link desc
PLAN (STATS_RESULTS ORDER I_STATS_RESULTS_LINK_SORT)

running the main part of the query manually

select first(1) result, recdate, rectime from stats_results
where recdate = '10-dec-2005' and
(rectime >= addminute( current_time,-60) and rectime < addminute(
current_time,10)) and
branchcode = 'GAR' and
reportlink = 8
order by link desc

results in 46 indexed reads on stats_results, prep = 0.032, processed on
0.187

The reason for the addminutes is that sometimes stats are not uploaded due
to transmission probems etc so the fudge factor keeps the graphs looking
pretty, data older than 1 hour means the branch is offline for all intents
and purposes.

Server is 1.5.3 SS.
I know there is an elegant solution staring me in the face but I can't get a
grip on it.

TIA