Links

MSCEWI1023

PERFORMANCE REVIEW - THIS LOOP CONTAINS AN INSERT, DELETE OR UPDATE STATEMENT

Severity

Low.

Description

This warning indicates a possible consideration that the user should have in terms of performance.

Example Code

Input Code:

REPLACE PROCEDURE Database1.Proc1()
BEGIN
DECLARE lNumber INTEGER DEFAULT 1;
FOR class1 AS class2 CURSOR FOR
SELECT COL0,
TRIM(COL1) AS COL1ALIAS,
TRIM(COL2),
COL3
FROM someDb.prefixCol
DO
INSERT INTO TempDB.Table1 (:lgNumber, :lNumber, (',' || :class1.ClassCD || '_Ind CHAR(1) NOT NULL'));
SET lNumber = lNumber + 1;
END FOR;
END;

Output Code:

CREATE OR REPLACE PROCEDURE Database1.PUBLIC.Proc1 ()
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
// REGION SnowConvert Helpers Code
var LNUMBER = 1;
//** MSC-WARNING - MSCEWI1023 - PERFORMANCE REVIEW - THIS LOOP CONTAINS AN INSERT, DELETE OR UPDATE STATEMENT **
for(var CLASS2 = new CURSOR(`SELECT
COL0,
TRIM(COL1) AS COL1ALIAS,
TRIM(COL2),
COL3
FROM someDb.PUBLIC.prefixCol`,[],false).OPEN();CLASS2.NEXT();) {
let CLASS1 = CLASS2.CURRENT;
EXEC(`INSERT INTO TempDB.PUBLIC.Table1 VALUES (:lgNumber, :1, (',' || :/*** MSC-WARNING - MSCEWI1026 - THE VARIABLE class1.ClassCD MAY REQUIRE A CAST TO DATE, TIME OR TIMESTAMP ***/
:2 || '_Ind CHAR(1) NOT NULL'))`,[LNUMBER,CLASS1.CLASSCD]);
LNUMBER = LNUMBER + 1;
}
CLASS2.CLOSE();
$$;
Last modified 11mo ago