Hi,
I use this script to update a field with progressive number.
The table is about 17.000 record and the rows to update is about 1100.
The execution time is 95 sec.
idx is a primary key
There is a method to improve speed (if I split select/update in two query from code the time is about 25s)?

Thanks

SCRIPT (PidBatt INTEGER, PidNome INTEGER, Pprog INTEGER, Pgiro INTEGER, OUT Ptotale INTEGER)
BEGIN
DECLARE Fgiro INTEGER;                        
DECLARE Fidx INTEGER;
DECLARE Result CURSOR FOR Stmt;
SET Fgiro= Pgiro;

START TRANSACTION;

   EXECUTE IMMEDIATE 'UPDATE giri SET giro= ?
WHERE idBatt = ? AND idNome = ? AND prog = ?' USING Pgiro, PidBatt, PidNome, Pprog;

PREPARE Stmt FROM 'SELECT idx FROM giri WHERE idBatt = ? AND idNome = ? AND prog > ? AND  tipo <= 2 AND invalid = false';
OPEN Result USING PidBatt, PidNome, Pprog;

FETCH FIRST FROM Result('idx') INTO Fidx;
 WHILE NOT EOF(Result) DO
   SET Fgiro = Fgiro+1;
   EXECUTE IMMEDIATE 'UPDATE giri SET giro= ? WHERE idx = ?' USING Fgiro, Fidx;
   FETCH NEXT FROM Result('idx') into Fidx;
 END WHILE;
CLOSE Result;

SET Ptotale= Fgiro;

COMMIT;
END

Hi Mirco,

Two things....

1. When retrieving your update candidate rows, use an INSENSITIVE cursor.  This prevents the retrieval query from being run each time you move through the result set.

2. Prepare your update statement ahead of time instead of using EXECUTE IMMEDIATE.  

Modified, but untested, script is below with changes indicated by --<<<


SCRIPT (PidBatt INTEGER, PidNome INTEGER, Pprog INTEGER, Pgiro INTEGER, OUT Ptotale INTEGER)
BEGIN
DECLARE Fgiro INTEGER;                        
DECLARE Fidx INTEGER;
DECLARE Result INSENSITIVE CURSOR FOR Stmt; --<<<
DECLARE UpdateStmt STATEMENT;  --<<<

SET Fgiro= Pgiro;

PREPARE UpdateStmt FROM 'UPDATE giri SET giro= ? WHERE idx = ?'; --<<<

START TRANSACTION;

  EXECUTE IMMEDIATE 'UPDATE giri SET giro= ?
WHERE idBatt = ? AND idNome = ? AND prog = ?' USING Pgiro, PidBatt, PidNome, Pprog;

PREPARE Stmt FROM 'SELECT idx FROM giri WHERE idBatt = ? AND idNome = ? AND prog > ? AND  tipo <= 2 AND invalid = false';
OPEN Result USING PidBatt, PidNome, Pprog;

FETCH FIRST FROM Result('idx') INTO Fidx;
WHILE NOT EOF(Result) DO
  SET Fgiro = Fgiro+1;
  EXECUTE UpdateStmt USING Fgiro, Fidx; --<<<
  FETCH NEXT FROM Result('idx') into Fidx;
END WHILE;
CLOSE Result;

SET Ptotale= Fgiro;

COMMIT;
END

Many thanks Terry!!
Fantastic from 95 to 0,1 sec


Terry Swiers wrote:

Hi Mirco,

Two things....

1. When retrieving your update candidate rows, use an INSENSITIVE cursor.  This prevents the retrieval query from being run each time you move through the result set.

2. Prepare your update statement ahead of time instead of using EXECUTE IMMEDIATE.  

Modified, but untested, script is below with changes indicated by --<<<


SCRIPT (PidBatt INTEGER, PidNome INTEGER, Pprog INTEGER, Pgiro INTEGER, OUT Ptotale INTEGER)
BEGIN
DECLARE Fgiro INTEGER;                        
DECLARE Fidx INTEGER;
DECLARE Result INSENSITIVE CURSOR FOR Stmt; --<<<
DECLARE UpdateStmt STATEMENT;  --<<<

SET Fgiro= Pgiro;

PREPARE UpdateStmt FROM 'UPDATE giri SET giro= ? WHERE idx = ?'; --<<<

START TRANSACTION;

  EXECUTE IMMEDIATE 'UPDATE giri SET giro= ?
WHERE idBatt = ? AND idNome = ? AND prog = ?' USING Pgiro, PidBatt, PidNome, Pprog;

PREPARE Stmt FROM 'SELECT idx FROM giri WHERE idBatt = ? AND idNome = ? AND prog > ? AND  tipo <= 2 AND invalid = false';
OPEN Result USING PidBatt, PidNome, Pprog;

FETCH FIRST FROM Result('idx') INTO Fidx;
WHILE NOT EOF(Result) DO
  SET Fgiro = Fgiro+1;
  EXECUTE UpdateStmt USING Fgiro, Fidx; --<<<
  FETCH NEXT FROM Result('idx') into Fidx;
END WHILE;
CLOSE Result;

SET Ptotale= Fgiro;

COMMIT;
END

> Fantastic from 95 to 0,1 sec

Sorry I wasn't able to do better.