CODEWITHSUNDEEP
performancedb2db2-luwdatabase-cursor
André Barbosa
André Barbosa

Reputation: 11

DB2 - stored procedure - very slow cursor when using IN parameter in fetch first clause

Version: db2 11.5 / AIX.

SLOW: (Using IN parameter)

CREATE OR REPLACE PROCEDURE TEST_INC (IN nINC INTEGER)
BEGIN
   FOR EACH_RECORD AS C1 CURSOR FOR 
      SELECT * 
      FROM MYTABLE 
      WHERE SEQ > 0 
      ORDER BY SEQ FETCH FIRST nINC ROWS ONLY
   DO
   ...
   END FOR;
END;

FAST (Using a fixed value)

CREATE OR REPLACE PROCEDURE TEST_INC (IN nINC INTEGER)
BEGIN
   FOR EACH_RECORD AS C1 CURSOR FOR 
      SELECT * 
      FROM MYTABLE 
      WHERE SEQ > 0 
      ORDER BY SEQ FETCH FIRST 5 ROWS ONLY
   DO
   ...
   END FOR
END;

Can anybody help me?

I would like, if possible, to continue using FOR.

Upvotes: 1

Views: 101

Answers (0)

Related Questions