Home » RDBMS Server » Performance Tuning » Procedure Cost Problem
Procedure Cost Problem [message #228753] Wed, 04 April 2007 01:33 Go to next message
subho_ORA
Messages: 2
Registered: April 2007
Junior Member
Hi,

In my application I wrote a procedure and access it from my VB application. Everything properly indexed but the cost is very high. Here I am sending my procedure....

AS
V_REGNNO CHAR(10);
RECEIPTNO CHAR(12);

CURSOR C1 IS
SELECT CASH_STATE.* FROM CASH_STATE WHERE NVL(STATUS,'A') != 'T' ;

BEGIN
DELETE FROM TRANSFER_LOG;
FOR RECSET IN C1 LOOP
BEGIN

V_REGNNO:=NULL;
RECEIPTNO:=NULL;


INSERT INTO CASH (35 files are there) VALUES --
(
RECSET.that 35 fields of CASH table);

IF V_REGNNO IS NOT NULL AND RECEIPTNO IS NOT NULL THEN
UPDATE RC_RECEIPT SET TRANSFLAG='T' WHERE
REGNNO=V_REGNNO AND RCPTNO=RECEIPTNO;

END IF;

UPDATE CASH_STATE SET STATUS='T' WHERE CHASISNO =RECSET.CHASISNO;
EXCEPTION
WHEN NO_DATA_FOUND THEN
INSERT INTO TRANSFER_LOG VALUES (RECSET.VEHREGNO,'NO RECEIPT');
END;
END LOOP;
COMMIT;
END;

two tables are there CASH_STATE and CASH. I just want to add data from CASH_STATE table to CASH table which has NULL value in STATUS field. But the problem is if there are too many that type of data then the cost is coming 2980 and the process is running very slow.

Plzzz help me..

[Updated on: Wed, 04 April 2007 06:16]

Report message to a moderator

Re: Procedure Cost Problem [message #228771 is a reply to message #228753] Wed, 04 April 2007 02:44 Go to previous message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
This article contains some tips for tuning batch PL/SQL.

Ross Leishman
Previous Topic: Query regarding Analyze table
Next Topic: Table Partition - Suggestion required
Goto Forum:
  


Current Time: Wed Nov 27 01:30:12 CST 2024