Optimise a Fetch cursor

srajanbose · New User Joined: 11 Oct 2004 Posts: 69 Location: chennai

Hi,

I have the below cursor which takes more time to fetch.Could anyone suggest how t optimise the cursor.

EXEC SQL DECLARE CURS-EPCF CURSOR FOR
SELECT EPCF_REF,
EPCF_CPT_CLI,
EPCF_CPT_FOUR,
EPCF_CD_TYP_SOLLOG
FROM TDEPCF
WHERE ( (EPCF_CPT_CLI = :SYS-USINE AND
(EPCF_CD_TYP_SOLLOG = 'E' OR
EPCF_CD_TYP_SOLLOG = 'I' ))
OR
(EPCF_CPT_FOUR = :SYS-USINE AND
EPCF_CD_TYP_SOLLOG = 'S' ) )

AND EPCF_DT_APPLI <= :W-TIMESTAMP
AND EPCF_DT_FIN > :W-DT-TRT
AND EPCF_CD_LOCA = :EPCF-CD-LOCA
FOR FETCH ONLY
END-EXEC.

Thanks in advance.

Regards,
Rajan BOSE

enrico-sorichetti · Posted: Mon Aug 23, 2010 2:39 pm Post subject: Reply to: Optimise a Fetch cursor

srajanbose · New User Joined: 11 Oct 2004 Posts: 69 Location: chennai

Hi,

As per my understanding the fetch takes more time in the below step

WHERE ( (EPCF_CPT_CLI = :SYS-USINE AND
(EPCF_CD_TYP_SOLLOG = 'E' OR
EPCF_CD_TYP_SOLLOG = 'I' ))
OR
(EPCF_CPT_FOUR = :SYS-USINE AND
EPCF_CD_TYP_SOLLOG = 'S' ) )

Is there any other optimised way to declare the cursor?

dbzTHEdinosauer · Posted: Mon Aug 23, 2010 2:44 pm Post subject:

srajanbose · New User Joined: 11 Oct 2004 Posts: 69 Location: chennai

Hi Dick,

Thanks.I have solved the problem.

expat · Posted: Mon Aug 23, 2010 4:59 pm Post subject:

Perhaps you may wish to post the resolution - in the vain hope that someone else with a similar question might actaully use the search button

srajanbose · New User Joined: 11 Oct 2004 Posts: 69 Location: chennai

Hi,

Sorry for the late response.I haave created a new index to solve my problem.

Regards,
Rajan BOSE.