> For each branchcode (orderselect) you run the same query on
> stats_reports, that means that if you have, say 50 branchcodes, then
> you are executing the exact same select 50 times. Since this select is
> also using NATURAL, it can be pretty time consuming. I'd rather go for
> a select with two joins than doing what you are doing (I just looked
> superficially at your code, there may be things that make this
> impossible).

Hi Sven

Thanks for having a look. There was a reason for doing it this way but I'm
not sure what it was. I am going to rewrite it the way you suggest and I'll
let you know what breaks.

Thanks
Gary

>> 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
>>
>> PLAN (BRANCHES ORDER I_BRANCHES_ORDERAPPEND)
>> PLAN SORT ((STATS_REPORTS NATURAL))
>> PLAN (STATS_RESULTS ORDER I_STATS_RESULTS_LINK_SORT)