Stored procedure performance (DELETES VERY SLOW)

[hikums]

Hi,

Can someone recommend a better approach, I already have clustered
indexes on the t1 column in table td.
This takes more than 30 hours to complete!!

The slowdown is with deletes within the LOOP/END LOOP

TIA,
Kums

--delete old records
CREATE PROCEDURE DELETE_SP()
RESULT SETS 1 LANGUAGE SQL
BEGIN
DECLARE v_update_flag INTEGER;
SET v_update_flag = 0;
DELETE FROM td;

INSERT INTO td ( PK )
SELECT distinct leadseq As PK FROM assignment WHERE
created < CURRENT TIMESTAMP - 24 MONTHS;
UPDATE td SET T1 = 'assignmentleadseq';

SELECT count(*) INTO v_update_flag FROM td;

IF v_update_flag 0 THEN

BEGIN

INSERT INTO td ( T1, PK )
SELECT 'assignmentseqid' As T1, seqid As PK
FROM assignment WHERE leadseq IN
(SELECT PK FROM td WHERE T1 =
'assignmentleadseq' );
COMMIT ;
INSERT INTO td ( T1, PK )
SELECT 'mapresult' As T1, id As PK FROM
mapresult WHERE assignmentseq IN
(SELECT PK FROM td WHERE T1 =
'assignmentseqid');
COMMIT ;
INSERT INTO td ( T1, PK )
SELECT 'executerulecommand' As T1, id As PK
FROM executerulecommand WHERE mapresultid IN
(SELECT PK FROM td WHERE T1 =
'mapresult');
COMMIT ;
INSERT INTO td ( T1, PK )
SELECT 'progressstatus' As T1,
progress_status_id As PK FROM command WHERE id IN
(SELECT PK FROM td WHERE T1 =
'executerulecommand');
COMMIT ;
INSERT INTO td ( T1, PK )
SELECT 'leadexseq' As T1, leadexseq As PK FROM
lead WHERE seqid IN
(SELECT PK FROM td WHERE T1 =
'assignmentleadseq');
COMMIT ;
INSERT INTO td ( T1, PK )
SELECT 'needsid' As T1, needsid As PK FROM lead
WHERE seqid IN
(SELECT PK FROM td WHERE T1 =
'assignmentleadseq');
COMMIT ;
INSERT INTO td ( T1, PK )
SELECT 'detailid' As T1, detailid As PK FROM
lead WHERE seqid IN
(SELECT PK FROM td WHERE T1 =
'assignmentleadseq');
COMMIT ;
LOOP

DELETE FROM command WHERE id IN
(SELECT PK FROM td WHERE T1 =
'executerulecommand');
COMMIT ;
DELETE FROM executerulecommand WHERE id IN
(SELECT PK FROM td WHERE T1 =
'executerulecommand');
COMMIT ;
DELETE FROM progress_status WHERE id IN
(SELECT PK FROM td WHERE T1 =
'progressstatus');
COMMIT ;
DELETE FROM simpleconditional WHERE result IN
(SELECT PK FROM td WHERE T1 =
'mapresult');
COMMIT ;
DELETE FROM mappredicate WHERE rowbp IN
(SELECT id FROM maprow WHERE result IN
(SELECT PK FROM td WHERE T1 =
'mapresult') );
COMMIT ;
DELETE FROM maprow WHERE result IN
(SELECT PK FROM td WHERE T1 =
'mapresult');
COMMIT ;
DELETE FROM mapresult WHERE id IN
(SELECT PK FROM td WHERE T1 =
'mapresult');
COMMIT ;
DELETE FROM assignmentattachments WHERE
assignmentseq IN
(SELECT PK FROM td WHERE T1 =
'assignmentseqid');
COMMIT ;
DELETE FROM leadobserver WHERE assignseq IN
(SELECT PK FROM td WHERE T1 =
'assignmentseqid');
COMMIT ;
DELETE FROM mapdestinations WHERE
suggestedassignid IN
(SELECT id FROM suggestedassignment
WHERE assignment_seqid IN
(SELECT PK FROM td WHERE T1 =
'assignmentseqid') );
COMMIT ;
DELETE FROM mapdestinations WHERE mapresultid
IN
(SELECT PK FROM td WHERE T1 =
'mapresult');
COMMIT ;
DELETE FROM suggestedassignment WHERE
assignment_seqid IN
(SELECT PK FROM td WHERE T1 =
'assignmentseqid');
COMMIT ;
DELETE FROM productinterest WHERE leadseq IN
(SELECT PK FROM td WHERE T1 =
'assignmentleadseq');
COMMIT ;
DELETE FROM sale WHERE seqid IN
(SELECT saleid FROM leadsales WHERE
leadid IN
(SELECT PK FROM td WHERE T1 =
'assignmentleadseq') );
COMMIT ;
DELETE FROM leadsales WHERE leadid IN
(SELECT PK FROM td WHERE T1 =
'assignmentleadseq');
COMMIT ;
DELETE FROM assignment WHERE seqid IN
(SELECT PK FROM td WHERE T1 =
'assignmentseqid');
COMMIT ;
DELETE FROM lead WHERE seqid IN
(SELECT PK FROM td WHERE T1 =
'assignmentleadseq');
COMMIT ;
DELETE FROM leadextensions WHERE seqid IN
(SELECT PK FROM td WHERE T1 =
'leadexseq');
COMMIT ;
DELETE FROM historyrecord WHERE objectid IN
(SELECT PK FROM td WHERE T1 =
'detailid');
COMMIT ;
DELETE FROM notes WHERE objectid IN
(SELECT PK FROM td WHERE T1 =
'detailid');
COMMIT ;
DELETE FROM notes WHERE objectid IN
(SELECT PK FROM td WHERE T1 =
'needsid');
COMMIT ;
DELETE FROM detail WHERE seqid IN
(SELECT PK FROM td WHERE T1 =
'detailid');
COMMIT ;
DELETE FROM detail WHERE seqid IN
(SELECT PK FROM td WHERE T1 =
'needsid');

COMMIT ;
END LOOP;
END;
END IF;


END

[Tonkuma]

How about create addditional index?
CREATE INDEX td_T1PK ON td (T1, PK);

And, why you are using LOOP? I couldn't find any statement to exit the
loop(Ex: LEAVE, RETURN).

[hikums]

Tonkuma wrote:

How about create addditional index?
CREATE INDEX td_T1PK ON td (T1, PK);

And, why you are using LOOP? I couldn't find any statement to exit the
loop(Ex: LEAVE, RETURN).

I did not understand the LOOP portion as well. May be it is specific to
SQL/PL??!!

I did a online reorg and rebind on the package for the SP while it was
running in the 17th hour, and it completed in 2 hours. Will know more
in next week's run.