By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,558 Members | 1,068 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,558 IT Pros & Developers. It's quick & easy.

Need to improve my stored procedure , your comments are welcome!!

P: n/a
The following SP removes records from table A if they exist in table
B.

Table A - TEST.TABLE_X has 30 million records
Table B - TEST.TABLE_X_DELTA has 5 million records.

Appx 4 million records shoud be deleted in Table A. However my query
moves the cursor one record at a time. So this is extremely slow. The
reason I have to use a SP is bec I need to avoid fulling the log
space. Thus I need to commit frequently.

I will appreciate if you can guide me in the right direction.
Thanks a bunch!
DECLARE VAR_CIF_ID DECIMAL(9, 0);

DECLARE TABLE_X_DELTA_CRSR CURSOR WITH HOLD FOR
SELECT B.TABLE_X_CIF_ID
FROM TEST.TABLE_X_DELTA B, TEST.TABLE_X A
WHERE A.TABLE_X_AR_ID=B.TABLE_X_AR_ID
AND A.TABLE_X_CIF_ID=B.TABLE_X_CIF_ID;

OPEN TABLE_X_DELTA_CRSR;

LOOP_TABLE_X_DELTA : LOOP

SET VAR_CIF_ID = NULL;
FETCH TABLE_X_DELTA_CRSR INTO VAR_CIF_ID;

IF VAR_CIF_ID IS NULL THEN LEAVE LOOP_TABLE_X_DELTA; END IF;

DELETE FROM TEST.TABLE_X WHERE TABLE_X_CIF_ID = VAR_CIF_ID;
COMMIT;

END LOOP LOOP_TABLE_X_DELTA;

CLOSE TABLE_X_DELTA_CRSR;

END;
Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
"Metin Esat" <me****@hotmail.com> wrote in message
news:d7**************************@posting.google.c om...
The following SP removes records from table A if they exist in table
B.

Table A - TEST.TABLE_X has 30 million records
Table B - TEST.TABLE_X_DELTA has 5 million records.

Appx 4 million records shoud be deleted in Table A. However my query
moves the cursor one record at a time. So this is extremely slow. The
reason I have to use a SP is bec I need to avoid fulling the log
space. Thus I need to commit frequently.

I will appreciate if you can guide me in the right direction.
Thanks a bunch!
DECLARE VAR_CIF_ID DECIMAL(9, 0);

DECLARE TABLE_X_DELTA_CRSR CURSOR WITH HOLD FOR
SELECT B.TABLE_X_CIF_ID
FROM TEST.TABLE_X_DELTA B, TEST.TABLE_X A
WHERE A.TABLE_X_AR_ID=B.TABLE_X_AR_ID
AND A.TABLE_X_CIF_ID=B.TABLE_X_CIF_ID;

OPEN TABLE_X_DELTA_CRSR;

LOOP_TABLE_X_DELTA : LOOP

SET VAR_CIF_ID = NULL;
FETCH TABLE_X_DELTA_CRSR INTO VAR_CIF_ID;

IF VAR_CIF_ID IS NULL THEN LEAVE LOOP_TABLE_X_DELTA; END IF;

DELETE FROM TEST.TABLE_X WHERE TABLE_X_CIF_ID = VAR_CIF_ID;
COMMIT;

END LOOP LOOP_TABLE_X_DELTA;

CLOSE TABLE_X_DELTA_CRSR;

END;


One thing that will help is only commit every 100 - 1000 deletes. You can
set up a counter in your loop and then reset the counter with the commit.
Each commit takes longer than the delete.

I assume you have the proper indexes set up?
Nov 12 '05 #2

P: n/a
Try this:
DECLARE SQLCODE INTEGER;

LOOP_TABLE_X_DELTA : LOOP
DELETE FROM
(SELECT 1 FROM TEST.TABLE_X AS A
WHERE EXISTS(SELECT 1
FROM TEST.TABLE_X_DELTA B
WHERE A.TABLE_X_AR_ID=B.TABLE_X_AR_ID
AND A.TABLE_X_CIF_ID=B.TABLE_X_CIF_ID)
FETCH FIRST 1000 ROWS ONLY) AS D
IF SQLCODE = 100 THEN LEAVE LOOP_TABLE_X_DELTA; END
COMMIT;
END LOOP;
COMMIT;

Of course you can play with the value in FETCH FIRST.

Cheers
Serge
Nov 12 '05 #3

P: n/a
Try this:
DECLARE SQLCODE INTEGER;

LOOP_TABLE_X_DELTA : LOOP
DELETE FROM
(SELECT 1 FROM TEST.TABLE_X AS A
WHERE EXISTS(SELECT 1
FROM TEST.TABLE_X_DELTA B
WHERE A.TABLE_X_AR_ID=B.TABLE_X_AR_ID
AND A.TABLE_X_CIF_ID=B.TABLE_X_CIF_ID)
FETCH FIRST 1000 ROWS ONLY) AS D
IF SQLCODE = 100 THEN LEAVE LOOP_TABLE_X_DELTA; END IF;
COMMIT;
END LOOP;
COMMIT;

Of course you can play with the value in FETCH FIRST.

Cheers
Serge
Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.