Home » SQL & PL/SQL » SQL & PL/SQL » Tuning (PL SQL)
Tuning [message #405702] Fri, 29 May 2009 02:33 Go to next message
anakin
Messages: 13
Registered: May 2009
Junior Member
Hi,
I am tuning some sqls. Please suggest which of the following option I should use.

*************Using Curser***********
IS
varCursorMsgType tbA.MsgType%Type;

CURSOR curChangeMsgStatus IS
SELECT T.MsgType
FROM tbA T
WHERE T.OC_STATUS = 'O' AND
T.PARTYA_ALIAS_INT = pPartA_AliaInt AND
T.PARTYB_ALIAS_INT = pPartB_AliaInt AND
T.ExpDate >= SYSDATE AND
T.MsgType = pMsgType
AND T.Backoffice_grp = pBackoffice -- ASPAC
;

BEGIN
OPEN curChangeMsgStatus;
FETCH curChangeMsgStatus INTO varCursorMsgType;
IF curChangeMsgStatus%FOUND THEN
pMsgStatus := 'VOID';
pMsgOrigStatus := 'VOID';
/* pac_Global.pro_AuditTrailPublic('SYSTEM', 'M', 'I', 'TB_CMS_FX_REC', varSenderRef||'//'||varMsgType||'//'||varPartyA||'//'||varPartyB||
'MESSAGE STATUS CHANGED TO VOID');*/
END IF;
CLOSE curChangeMsgStatus;
********************************************

***************Using Select...INTO**********
IS
varMsgType tbA.MsgType%Type;

BEGIN
SELECT T.MsgType INTO varMsgType
FROM tbA T
WHERE T.OC_STATUS = 'O' AND
T.PARTYA_ALIAS_INT = pPartA_AliaInt AND
T.PARTYB_ALIAS_INT = pPartB_AliaInt AND
T.ExpDate >= SYSDATE AND
T.MsgType = pMsgType
AND T.Backoffice_grp = pBackoffice -- ASPAC
;

IF SQL%FOUND THEN
pMsgStatus := 'VOID';
pMsgOrigStatus := 'VOID';

END IF;
****************************
Re: Tuning [message #405704 is a reply to message #405702] Fri, 29 May 2009 02:38 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
The two won't differ in performance (as you could have seen yourself if you'd have run a test)
However, your second version is semantically incorrect: a select into will raise a no_data_found exception if no row was found, so SQL%NOTFOUND will never be true
Re: Tuning [message #405723 is a reply to message #405704] Fri, 29 May 2009 04:10 Go to previous messageGo to next message
anakin
Messages: 13
Registered: May 2009
Junior Member
Thanks for your response.
I have handled the exception after the IF loop.
But as far as tuning concerned, the Curser needs Open-fetch-close operations but not for select..into.
like this there are many cursers been used in several procedures.
I thought if I change the cursers where I can put select..into might help me collectively.

Waiting for your suggestions!!!!
Re: Tuning [message #405731 is a reply to message #405723] Fri, 29 May 2009 04:22 Go to previous messageGo to next message
anakin
Messages: 13
Registered: May 2009
Junior Member
Hi Frank,
I have been given a performance tuning task. But its been told that I should not touch the indexes in the begining, only I have to go thru the code and find out wherever there needs an improvement in the code.
Re: Tuning [message #405732 is a reply to message #405731] Fri, 29 May 2009 04:29 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
If you only gonna fetch a single row, focus on the query itself, not on the PL/SQL construct.
Re: Tuning [message #405737 is a reply to message #405731] Fri, 29 May 2009 04:41 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The big thing to look for in Pl/Sql coding is Loops with pieces of SQL inside them where the Sql can be altered to affect all of the rows that the loop requires and can them be run once.
Re: Tuning [message #405796 is a reply to message #405731] Fri, 29 May 2009 08:42 Go to previous message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Adding to @Frank's comments I will be inclined to the second approach (select into) rather than the first approach (Open Fetch and close). Reason being is that I don't want to scroll back and forth on the package just to find out what the cursor is doing. I don't want to opt for a explicit cursor unless and until it is absolutely necessary.

Just a thought.

Regards

Raj
Previous Topic: PL/SQL integer null value or NVL?
Next Topic: Procedure doent insert.
Goto Forum:
  


Current Time: Tue Dec 06 08:31:21 CST 2016

Total time taken to generate the page: 0.09467 seconds