DELETE causing performance issues

[pike]

8.1 FP11 on AIX 5.3.

The following DELETE is poorly performing and causing lock escalation
(and subsequent deadlock time-outs):

DELETE FROM submission_log
WHERE subm_id = ?
OR subm_id = (SELECT orig_subm_id FROM submission WHERE subm_id = ?)

Optimizer Plan:

DELETE
( 2)
/------------/ \---\
NLJOIN
Table:
( 3)
/----------------/ \---------------\
SUBMISSION_LOG
FETCH FETCH
( 4) (----)
/ \ /--------/ \
IXSCAN Table: RIDSCN
Table:
( 4) ( 7)

| SUBMISSION /--------/ \--------\
SUBMISSION_LOG
Index: SORT SORT
( 8) ( 10)
PK_SUBMISSION | |
IXSCAN IXSCAN
( 9) ( 11)
/ \ / \
Index: Table: Index: Table:
I_SUBM_LOG_S_A SUBMISSION_LOG I_SUBM_LOG_S_A
SUBMISSION_LOG
Can anyone provide (SQL?) tips other than to increase LOCKTIMEOUT,
LOCKLIST that might improve matters?

Many thanks.

[Serge Rielau]

pike wrote:

8.1 FP11 on AIX 5.3.

The following DELETE is poorly performing and causing lock escalation
(and subsequent deadlock time-outs):

DELETE FROM submission_log
WHERE subm_id = ?
OR subm_id = (SELECT orig_subm_id FROM submission WHERE subm_id = ?)

OR predicates *shudder*
Try this (note the UNION, NOT UNION ALL)
MERGE INTO submission_log T
USING (SELECT orig_subm_id FROM submission WHERE subm_id = ?
UNION
VALUES (CAST ? AS <whatever>)) S(subm_id)
ON S.subm_id = T.submid
WHEN MATCHED THEN DELETE

If you get lock escalation because you delete so many rows there is
little to be done about that other than breaking down the statement into
chunks.
E.g. deleting 1000 rows at a time or so.

Cheers
Serge

PS: You may want to consider db2set DB2_SKIP_DELETED = yes
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/

[Shashi Mannepalli]

Can u paste the db2exfmt output for this alternative query

DELETE FROM submission_log

WHERE coalsce(subm_id,subm_id) = ?
OR subm_id= (SELECT orig_subm_id FROM submission WHERE coalsce(subm_id,subm_id) = ?)

cheers...
Shashi Mannepalli
pike wrote:

8.1 FP11 on AIX 5.3.

The following DELETE is poorly performing and causing lock escalation
(and subsequent deadlock time-outs):

DELETE FROM submission_log
WHERE subm_id = ?
OR subm_id = (SELECT orig_subm_id FROM submission WHERE subm_id = ?)

Optimizer Plan:

DELETE
( 2)
/------------/ \---\
NLJOIN
Table:
( 3)
/----------------/ \---------------\
SUBMISSION_LOG
FETCH FETCH
( 4) (----)
/ \ /--------/ \
IXSCAN Table: RIDSCN
Table:
( 4) ( 7)

| SUBMISSION /--------/ \--------\
SUBMISSION_LOG
Index: SORT SORT
( 8) ( 10)
PK_SUBMISSION | |
IXSCAN IXSCAN
( 9) ( 11)
/ \ / \
Index: Table: Index: Table:
I_SUBM_LOG_S_A SUBMISSION_LOG I_SUBM_LOG_S_A
SUBMISSION_LOG
Can anyone provide (SQL?) tips other than to increase LOCKTIMEOUT,
LOCKLIST that might improve matters?

Many thanks.

[pike]

Thank you very much Serge - I'll work on those suggestions and come
back to you.

Hi Shashi - the Optimizer Plan is more 'simple', but the associated
cost is much higher for the COALESCE SQL (3232352 timerons v 5790
timerons).

DELETE
( 2)
/------/ \
NLJOIN Table:
( 3) EPO3LINE
/----/ \---\ SUBMISSION_LOG
FETCH IXSCAN
( 4) ( 3)
| / \
IXSCAN Index: Table:
( 4) EPO3LINE EPO3LINE
| I_SUBM_LOG_S_A SUBMISSION_LOG
Index:
EPO3LINE
PK_SUBMISSION

[pike]

Serge - I've been told that the SQL you suggested is not
MAXDB-compatible, therefore currently unacceptable :-( . The developer
is now looking to split the DELETE into two separate statements. Thanks
again for your help.