Like emb_blaster, I know very little about cursors, though of course I agree that using 40 seconds for one million and minutes for 20000 sounds strange. Are there anything else going on at the same time that could explain this (e.g. sweep)? Are there anything different in these two cases, e.g. index selectivity or that the slow version is the first thing that happens after connecting? Of course it is possible to think of possible reasons like the optimizer choosing a different plan. However, you have no WHERE clause in your procedure and then the optimizer has to choose NATURAL anyway. In Firebird 1.5, I have observed slowness when selecting indexed fields containing lots of NULLs, though I think that might have been when the indexed field was used in JOINs. Of course, you also have the common reasons like garbage buildup (visible through a gap between oldest and next transaction) or other queries consuming 100% of CPU. But to sum up, I have no clue why your SP varies that much in time.

But what if you simply do

UPDATE FNDY_LINK FL
SET FL.STATUS = CASE
WHEN FL.ENSEMBLE = 'N' THEN 'N'
WHEN FL.TOTWGHT < (SELECT FT.LOWER_THRESHOLD
FROM FNDY_THRESHOLDS FT) THEN 'R'
WHEN FL.TOTWGHT >= (SELECT FT2.LOWER_THRESHOLD
FROM FNDY_THRESHOLDS FT2) AND
FL.TOTWGHT < (SELECT FT3.UPPER_THRESHOLD
FROM FNDY_THRESHOLDS FT3) THEN 'P'
ELSE 'D'
END,
FL.SOURCE = 'R'

Is this equally time 'varying'?

Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of chevretteantoine
Sent: 14. desember 2009 21:11
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Firebird speed not constant

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