By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,985 Members | 1,712 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,985 IT Pros & Developers. It's quick & easy.

foreign key constraint slows down UPDATE performance

P: n/a
I encountered a performance problem with UPDATE statement and foreign
key constraints.

There are two tables involved: A parent table A and a child table B:

create table A(
ID CHAR(15) NOT NULL CONSTRAINT APK PRIMARY KEY,
val NUMERIC(10))

create table B(ID CHAR(15) NOT NULL CONSTRAINT BPK PRIMARY KEY,
AID CHAR(15),
val NUMERIC(10))

CREATE INDEX B_AID ON B(AID)

ALTER TABLE B
ADD CONSTRAINT TOPARENT FOREIGN KEY(AID)
REFERENCES A ON DELETE CASCADE
Table A is populated with 7100 rows and table B with 41300 rows.

If I run an UPDATE statement like this one:

UPDATE B SET AID='qwertzuiopasdfg' WHERE ID='yxcvbn123456789'

then this statement takes an average of about 11ms. If I drop the
foreign key constraint, then it takes only about 2ms in average.
(Adding the constraint again leads to 11ms again)

Is this normal behavior, that the presence of a foreign key constraint
slows down UPDATE performance by a factor of 5? I guess that it is not
normal at all, so what I'm doing wrong? Perhaps A bad configuration?

(I'm using DB2 UDB 8.1 PE with FixPak 2, Windows XP)

Gerald
Nov 12 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
How does the plan look like? Are you having appropriate indexes?

Cheers
Serge
Nov 12 '05 #2

P: n/a
"Serge Rielau" <sr*****@ca.eyebeem.com> wrote in message news:<bo**********@hanover.torolab.ibm.com>...
How does the plan look like? Are you having appropriate indexes?


Anything wrong with the following plan?

Access Plan:
-----------
Total Cost: 43.0093
Query Degree: 1

Rows
RETURN
( 1)
Cost
I/O
|
0.333333
FILTER
( 2)
43.0093
4
+-------------+----------+--+-------------------+
1 1 0 0.04
TBSCAN TBSCAN FILTER FILTER
( 3) ( 4) ( 9) ( 11)
0.000126588 21.4686 0.00228914 21.5346
0 2 0 2
| | | |
1 1 1 1
TABFNC: SYSIBM TEMP TBSCAN NLJOIN
GENROW ( 5) ( 10) ( 12)
21.4011 0.000126588 21.5319
2 0 2
| | /----+---\
1 1 1 1
UPDATE TABFNC: SYSIBM TBSCAN IXSCAN
( 6) GENROW ( 13) ( 14)
21.3662 21.4686 0.0633543
2 2 0
/----+---\ | |
1 62 1 94
FETCH TABLE: A62_181 TEMP INDEX: A62_181
( 7) B ( 5) APK
10.7125 21.4011
1 2
/----+----\
1 62
IXSCAN TABLE: A62_181
( 8) B
0.0568729
0
|
62
INDEX: A62_181
BPK
Nov 12 '05 #3

P: n/a
Looks OK. We are working to make TEMP(5)l go away, but until then the plan
is fine.

Cheers
Serge
Nov 12 '05 #4

P: n/a
"Serge Rielau" <sr*****@ca.eyebeem.com> wrote in message news:<bo**********@hanover.torolab.ibm.com>...
Looks OK. We are working to make TEMP(5)l go away, but until then the plan
is fine.


TEMP! This is the deciding clue, Thank You! My system temporary
tablespace was DMS and I changed it now to SMS to see what would
happen, and: the performance problem has gone away!

But I always thought DMS means high-performance. Why does this not
hold for temporary tablespaces?

Gerald
Nov 12 '05 #5

P: n/a
"Gerald Khin" <g.****@ids-scheer.de> wrote in message
news:4c**************************@posting.google.c om...
"Serge Rielau" <sr*****@ca.eyebeem.com> wrote in message

news:<bo**********@hanover.torolab.ibm.com>...
Looks OK. We are working to make TEMP(5)l go away, but until then the plan is fine.


TEMP! This is the deciding clue, Thank You! My system temporary
tablespace was DMS and I changed it now to SMS to see what would
happen, and: the performance problem has gone away!

But I always thought DMS means high-performance. Why does this not
hold for temporary tablespaces?

Gerald


SMS automatically allocates space as needed. DMS only has the space you
allocate via containers. When using DMS you should increase the size of the
temporary tablespace to several times as big as the largest table (if
possible). DMS will only perform better if you allocate the containers in an
efficient manner (containers placed on separate physical drives and
controllers if possible). There are different optimal configurations for
regular disk and RAID striped disks.
Nov 12 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.