Hi list,

I have an SP that counts records on a big table according to some criteria.
The SP's params determine the set of records that will be counted.

The pb is that the *first* execution of the SP *after a GC* and for a *given
set of params* invariably takes about 5 times longer than subsequent
executions of the same SP with the given params.

If I then change the params to count another set of rows, again first
execution is longer than subsequent executions.

What could explain this ?

Notes :

- By GC, I mean indifferently a fresh restore, a SWEEP or a select count(*)
- All statements/actions above are issued in exclusive access.
- The stored proc looks like this :

CREATE PROCEDURE STAT_PRESCOUNT_PER_CTRY_PERIOD (
ACOUNTRY INTEGER,
APERIOD DATE)
RETURNS (
RESULT INTEGER)
AS
begin
select count(pres.id)
from Outlet O
join presence pres on (O.id = pres.outletid)
join sys_idlists_idx L2 on (pres.status = L2.id)
where
O.countryid = :acountry and L2.groupid = 2 and
pres.valuedate = :aperiod
into
:result;

suspend;
end

Regards.

--
Jerome