Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 4 of 4 total |
Slow script |
Tue, Jan 21 2020 12:13 PM | Permanent Link |
Mirco Malagoli | 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 |
Tue, Jan 21 2020 1:51 PM | Permanent Link |
Terry Swiers | 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 |
Wed, Jan 22 2020 3:08 AM | Permanent Link |
Mirco Malagoli | 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 |
Wed, Jan 22 2020 8:57 PM | Permanent Link |
Terry Swiers | > Fantastic from 95 to 0,1 sec Sorry I wasn't able to do better. |
This web page was last updated on Tuesday, April 30, 2024 at 03:55 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |