469,626 Members | 1,369 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

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

DELETE causing performance issues

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.

Sep 22 '06 #1
4 1764
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/
Sep 22 '06 #2
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.
Sep 22 '06 #3
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

Sep 25 '06 #4
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.

Sep 26 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

25 posts views Thread by Brian Patterson | last post: by
5 posts views Thread by sandy | last post: by
16 posts views Thread by robert | last post: by
115 posts views Thread by Mark Shelor | last post: by
12 posts views Thread by bernhard.willems | last post: by
14 posts views Thread by Michel Esber | last post: by
8 posts views Thread by Ratko | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.