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

How does the plan look like? Are you having appropriate indexes?

Cheers
Serge

"Serge Rielau" <srielau@ca.eyebeem.com> wrote in message news:<bo5g01$qoi$1@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

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

Cheers
Serge

"Serge Rielau" <srielau@ca.eyebeem.com> wrote in message news:<bo8s7e$cbq$1@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

"Gerald Khin" <g.khin@ids-scheer.de> wrote in message
news:4c8399c9.0311050155.119c9a26@posting.google.c om...
> "Serge Rielau" <srielau@ca.eyebeem.com> wrote in message
news:<bo8s7e$cbq$1@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.