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='qwertzuiop asdfg' WHERE ID='yxcvbn12345 6789'
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
