473,804 Members | 3,126 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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 1907
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_DELETE D = 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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

25
3496
by: Brian Patterson | last post by:
I have noticed in the book of words that hasattr works by calling getattr and raising an exception if no such attribute exists. If I need the value in any case, am I better off using getattr within a try statement myself, or is there some clever implementation enhancement which makes this a bad idea? i.e. should I prefer: if hasattr(self,"datum"): datum=getattr("datum") else: datum=None
5
4682
by: sandy | last post by:
Hi All, I am a newbie to MySQL and Python. At the first place, I would like to know what are the general performance issues (if any) of using MySQL with Python. By performance, I wanted to know how will the speed be, what is the memory overhead involved, etc during database specific operations (retrieval, update, insert, etc) when MySQL is used with Python.
2
1649
by: Unruled Boy | last post by:
1.The follow two ways to declare one object: any difference? especially its performance. a.Private m_objMyObject As MyObject=New MyObject() b.Private m_objMyObject As MyObject m_objMyObject=New MyObject 2.Any difference between a and b? a. For Each childItem In SomeItems Dim objData As DataObject=m_objOneSource.GetData(childItem.ID)
16
3880
by: robert | last post by:
been ruminating on the question (mostly in a 390/v7 context) of whether, and if so when, a row update becomes an insert/delete. i assume that there is a threshold on the number of columns of the table, or perhaps bytes, being updated where the engine just decides, screw it, i'll just make a new one. surfed this group and google, but couldn't find anything. the context: we have some java folk who like to parametize/
115
7661
by: Mark Shelor | last post by:
I've encountered a troublesome inconsistency in the C-language Perl extension I've written for CPAN (Digest::SHA). The problem involves the use of a static array within a performance-critical transform function. When compiling under gcc on my big-endian PowerPC (Mac OS X), declaring this array as "static" DECREASES the transform throughput by around 5%. However, declaring it as "static" on gcc/Linux/Intel INCREASES the throughput by...
12
3066
by: bernhard.willems | last post by:
We are using DB2 UDB version 8.1 fp7 & fp9 on AIX 5.2. On our databases we do massive inserts and deletes (millions of rows). The performance is dramatically reduced because of pseudo deleted keys. In a 10 minutes it goes from 18000 transaction per minute to 9000 and lower. When an index reorg with CLEANUP ONLY PAGES is executed, the number of transactions climbs again. But a few minutes later it drops again to 9000. We have to run the...
14
9653
by: Michel Esber | last post by:
Linux RH 4.0 running DB2 V8 FP 11. I have a table with ~ 11M rows and running DELETE statements is really slow. Deleting 1k rows takes more than 3 minutes. If I run select statements on the same table, I usually fetch rows in a reasonable time. The table has the following description: MACHINE_ID VARCHAR (24)
3
12348
by: Michel Esber | last post by:
Hello, Environment: DB2 LUW v8 FP15 / Linux I have a table with 50+ Million rows. The table structure is basically (ID - Timestamp). I have two main applications - one inserting rows, and the other reading/deleting rows. The 'deleter' application runs a MIN/MAX (timestamp) for each ID and, if the difference between min/max is greater than 1h, it reads all
8
1717
by: Ratko | last post by:
Say you have something like this: for item in myList: del item Would this actually delete the item from the list or just decrement the reference counter because the item in myList is not associated with name "item" anymore (but still is with myList)? In other words, is "item" a temporary reference to myList or is it actually that reference that myList has stored?
0
10320
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
9134
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7609
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6846
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5513
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5645
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4288
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
3806
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2981
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.