Hello, I have a simple store procedure (see at the end), it will update a table and set a variable to R,D,P depending on a threshold value.
The thing is sometime it will take 40 seconds to go through a 1 million record table and sometime 5 minutes to go through 20 000 records, that does not make sense.

The housekeeping on my database is set to 0 (isc_spb_prp_sweep_interval), I always sweep (isc_spb_rpr_sweep_db) the database before running the store procedure and the
isc_dpb_no_reserve is set to 0 (in order to leave space).

Any idea why the performance differ so much?


SET TERM ^ ;
ALTER PROCEDURE APPLY_THRESHOLDS
AS
DECLARE VARIABLE DUMMY INTEGER;
DECLARE VARIABLE T_CUTOFF_THRESHOLD INTEGER;
DECLARE VARIABLE T_LOWER_THRESHOLD INTEGER;
DECLARE VARIABLE T_UPPER_THRESHOLD INTEGER;
DECLARE VARIABLE T_TOTWGHT INTEGER;
DECLARE VARIABLE T_STATUS CHAR(1);
DECLARE VARIABLE T_ENSEMBLE CHAR(1);
DECLARE VARIABLE RESULT CHAR(1);

DECLARE STATUS_UPDATE CURSOR FOR (
SELECT
FNDY_LINK.TOTWGHT,
FNDY_LINK.STATUS,
FNDY_LINK.ENSEMBLE
FROM FNDY_LINK
FOR UPDATE OF
STATUS,SOURCE);
BEGIN
SELECT FNDY_THRESHOLDS.CUTOFF_THRESHOLD,
FNDY_THRESHOLDS.LOWER_THRESHOLD,
FNDY_THRESHOLDS.UPPER_THRESHOLD
FROM FNDY_THRESHOLDS
INTO :T_CUTOFF_THRESHOLD,
:T_LOWER_THRESHOLD,
:T_UPPER_THRESHOLD;


OPEN STATUS_UPDATE;
FETCH STATUS_UPDATE INTO
:T_TOTWGHT ,
:T_STATUS,
:T_ENSEMBLE;

WHILE(ROW_COUNT <> 0) DO BEGIN

IF(T_ENSEMBLE = 'N') THEN RESULT = 'N';
ELSE BEGIN
IF (T_TOTWGHT < T_LOWER_THRESHOLD) THEN BEGIN
RESULT='R';
END
ELSE IF (T_LOWER_THRESHOLD <= T_TOTWGHT AND T_TOTWGHT < T_UPPER_THRESHOLD) THEN BEGIN
RESULT='P';
END
ELSE RESULT='D';
END

UPDATE FNDY_LINK SET STATUS =:RESULT, SOURCE='R' WHERE CURRENT OF STATUS_UPDATE;

DUMMY = GEN_ID(GEN_FNDY_THRESHOLD,1);

FETCH STATUS_UPDATE INTO
:T_TOTWGHT ,
:T_STATUS,
:T_ENSEMBLE;
END
CLOSE STATUS_UPDATE;
EXECUTE PROCEDURE COUNT_RULE;
DUMMY = GEN_ID(GEN_FNDY_NL,1);
END^
SET TERM ; ^

Antoine Chevrette
Project Leader | Chargé de projet
Generalized Systems | Systèmes généralisés
R.H. Coats Building | Immeuble R.-H.-Coats / Floor | Étage 14 A
Statistics Canada | 100 Tunney's Pasture Driveway, Ottawa ON K1A 0T6
Statistique Canada | 100, promenade Tunney's Pasture, Ottawa ON K1A 0T6
Antoine.Chevrette@...
Telephone | Téléphone 613-951-9903
Facsimile | Télécopieur 613-951-4087
Government of Canada | Gouvernement du Canada