467,118 Members | 925 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,118 developers. It's quick & easy.

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

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
  • viewed: 1703
Share:
3 Replies
"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
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
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.

Similar topics

1 post views Thread by Dan Caron | last post: by
1 post views Thread by Christian BŁttner | last post: by
7 posts views Thread by Jeff Wang | last post: by
6 posts views Thread by segis bata | last post: by
4 posts views Thread by Simon Gare | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.