| Procedure Cost Problem [message #228753] |
Wed, 04 April 2007 01:33  |
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 |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
