FOREIGN KEY CHECK ON INSERT

[collincm]

Hi,

I am trying to optimize a table for inserts. Half of the timeron cost is in
the FK lookup!

These tables for example
CREATE TABLE FOO2(
FOO2_ID INTEGER NOT NULL CONSTRAINT FOO2_PK PRIMARY KEY,
DATA VARCHAR(200) NOT NULL,
LASTTIME TIMESTAMP NOT NULL);

CREATE TABLE FOO(
FOO_ID INTEGER NOT NULL CONSTRAINT FOO_PK PRIMARY KEY,
FOO2_ID INTEGER CONSTRAINT FOO_FK1 REFERENCES FOO2(FOO2_ID) ON DELETE
RESTRICT ON UPDATE RESTRICT,
STATUS CHAR(1) NOT NULL,
DATA VARCHAR(200) NOT NULL,
LASTTIME TIMESTAMP NOT NULL);

when I create an access plan for the following insert into FOO it shows a
lookup for the FOO2_ID value even though it is NULL!
INSERT INTO FOO VALUES(
1,
NULL,
'A',
'This is Data',
CURRENT TIMESTAMP);

I thought maybee the explain plan showed the typical results for any data
values so I tested inserts with the FK applied and with the FK removed.

I inserted 70% faster with the FK removed.

Can someone explain why it is doing the key scan for a value that cannot be
thier FOO2.FOO2_ID is NOT NULL?

Even if FOO2.FOO2_ID was nullable would it be appropiate to 'link' them?

Can I keep the standard RI checks but not do the FK check when NULL?

I believe I could accomplish this with custom triggers but I would think the
DBM would optimize this shortcut.

thank you for your time,

[Mark A]

"collincm" <co******@thath otmail.comservi ce> wrote in message
news:vs******** ****@corp.super news.com...

Hi,

I am trying to optimize a table for inserts. Half of the timeron cost is in the FK lookup!

These tables for example
CREATE TABLE FOO2(
FOO2_ID INTEGER NOT NULL CONSTRAINT FOO2_PK PRIMARY KEY,
DATA VARCHAR(200) NOT NULL,
LASTTIME TIMESTAMP NOT NULL);

CREATE TABLE FOO(
FOO_ID INTEGER NOT NULL CONSTRAINT FOO_PK PRIMARY KEY,
FOO2_ID INTEGER CONSTRAINT FOO_FK1 REFERENCES FOO2(FOO2_ID) ON DELETE
RESTRICT ON UPDATE RESTRICT,
STATUS CHAR(1) NOT NULL,
DATA VARCHAR(200) NOT NULL,
LASTTIME TIMESTAMP NOT NULL);

when I create an access plan for the following insert into FOO it shows a
lookup for the FOO2_ID value even though it is NULL!
INSERT INTO FOO VALUES(
1,
NULL,
'A',
'This is Data',
CURRENT TIMESTAMP);

I thought maybee the explain plan showed the typical results for any data
values so I tested inserts with the FK applied and with the FK removed.

I inserted 70% faster with the FK removed.

Can someone explain why it is doing the key scan for a value that cannot be thier FOO2.FOO2_ID is NOT NULL?

Even if FOO2.FOO2_ID was nullable would it be appropiate to 'link' them?

Can I keep the standard RI checks but not do the FK check when NULL?

I believe I could accomplish this with custom triggers but I would think the DBM would optimize this shortcut.

thank you for your time,

Just out of curiosity, try explaining this statement:

INSERT into foo
(foo_id, status ,data , lasttime )
values( 1, 'A', 'This is Data', current timestamp)

[collincm]

Nice idea but the explain plan still shows a check on FOO2.

"Mark A" <ma@switchboard .net> wrote in message
news:pW******** *********@news. uswest.net...

"collincm" <co******@thath otmail.comservi ce> wrote in message
news:vs******** ****@corp.super news.com...

Hi,

I am trying to optimize a table for inserts. Half of the timeron cost is

in

the FK lookup!

These tables for example
CREATE TABLE FOO2(
FOO2_ID INTEGER NOT NULL CONSTRAINT FOO2_PK PRIMARY KEY,
DATA VARCHAR(200) NOT NULL,
LASTTIME TIMESTAMP NOT NULL);

CREATE TABLE FOO(
FOO_ID INTEGER NOT NULL CONSTRAINT FOO_PK PRIMARY KEY,
FOO2_ID INTEGER CONSTRAINT FOO_FK1 REFERENCES FOO2(FOO2_ID) ON DELETE
RESTRICT ON UPDATE RESTRICT,
STATUS CHAR(1) NOT NULL,
DATA VARCHAR(200) NOT NULL,
LASTTIME TIMESTAMP NOT NULL);

when I create an access plan for the following insert into FOO it shows

a lookup for the FOO2_ID value even though it is NULL!
INSERT INTO FOO VALUES(
1,
NULL,
'A',
'This is Data',
CURRENT TIMESTAMP);

I thought maybee the explain plan showed the typical results for any data values so I tested inserts with the FK applied and with the FK removed.

I inserted 70% faster with the FK removed.

Can someone explain why it is doing the key scan for a value that cannot

be

thier FOO2.FOO2_ID is NOT NULL?

Even if FOO2.FOO2_ID was nullable would it be appropiate to 'link' them?

Can I keep the standard RI checks but not do the FK check when NULL?

I believe I could accomplish this with custom triggers but I would think

the

DBM would optimize this shortcut.

thank you for your time,

Just out of curiosity, try explaining this statement:

INSERT into foo
(foo_id, status ,data , lasttime )
values( 1, 'A', 'This is Data', current timestamp)

[Mark A]

"collincm" <co******@thath otmail.comservi ce> wrote in message
news:vs******** ****@corp.super news.com...

Nice idea but the explain plan still shows a check on FOO2.

I tested this myself using explain and got similar results.

Just to clarify, are you saying that you measured actual insert performance
and it ran 70% faster without the constraint even when the FK column is
null?

[collincm]

That is true. The tables were populated. about 25 timerons for the actual
insert and another 25 timerons for the fk check.
"Mark A" <ma@switchboard .net> wrote in message
news:AC******** **********@news .uswest.net...

"collincm" <co******@thath otmail.comservi ce> wrote in message
news:vs******** ****@corp.super news.com...

Nice idea but the explain plan still shows a check on FOO2.
I tested this myself using explain and got similar results.

Just to clarify, are you saying that you measured actual insert

performance and it ran 70% faster without the constraint even when the FK column is
null?

[Mark A]

"collincm" <co******@thath otmail.comservi ce> wrote in message
news:vs******** ****@corp.super news.com...

That is true. The tables were populated. about 25 timerons for the actual insert and another 25 timerons for the fk check.

I updated the card (in sysibm.systable s) for some tables in the sample
database and got the exact same numbers on the explain. Don't know why it is
doing that.

[AK]

timeron costs may be a little bit misleading.
I would try to compare real execution costs.
The reason: if the bufferpool is warm, then the index pages are
usually already in it. The optimizer never knows if the bufferpool is
warm or not

[collincm]

The 70% improvment was a real timings of around 1000 records, once with the
key, again without the key. I am purosly testing with a very small (default
1MB) buffer pool. I am sure I can increase it to help keep the index pages
of FOO2 in the pool, but that will make an uneccessary operation faster. I
don't want it to check at all. There is no logical reason why it should do
a key check for a NULL value. It is NULL.

"AK" <ak************ @yahoo.com> wrote in message
news:46******** *************** ***@posting.goo gle.com...

timeron costs may be a little bit misleading.
I would try to compare real execution costs.
The reason: if the bufferpool is warm, then the index pages are
usually already in it. The optimizer never knows if the bufferpool is
warm or not

[AK]

"collincm" <co******@thath otmail.comservi ce> wrote in message news:<vs******* *****@corp.supe rnews.com>...

The 70% improvment was a real timings of around 1000 records, once with the
key, again without the key. I am purosly testing with a very small (default
1MB) buffer pool. I am sure I can increase it to help keep the index pages
of FOO2 in the pool, but that will make an uneccessary operation faster. I
don't want it to check at all. There is no logical reason why it should do
a key check for a NULL value. It is NULL.

I would insert 1000 records with no NULLs at all,
then next 1000 records with only NULLs, but the FK constraint still
present, and compare real execution costs (db2batch output)

[Jeremy Rickard]

"collincm" <co******@thath otmail.comservi ce> wrote in message news:<vs******* *****@corp.supe rnews.com>...

The 70% improvment was a real timings of around 1000 records, once with the
key, again without the key. I am purosly testing with a very small (default
1MB) buffer pool. I am sure I can increase it to help keep the index pages
of FOO2 in the pool, but that will make an uneccessary operation faster. I
don't want it to check at all. There is no logical reason why it should do
a key check for a NULL value. It is NULL.

"AK" <ak************ @yahoo.com> wrote in message
news:46******** *************** ***@posting.goo gle.com...

timeron costs may be a little bit misleading.
I would try to compare real execution costs.
The reason: if the bufferpool is warm, then the index pages are
usually already in it. The optimizer never knows if the bufferpool is
warm or not

I just tried your example insert out with DB2 v8 for LUW (DDL as
provided, no statistics gathered). My access plan also shows a FK
lookup, but as I expected there are no FOO2 PK index accesses when I
run the insert with NULL (i.e. null = index lookups are ignored at
run-time).

Could you try using the snapshot monitor to see how many bufferpool
logical index reads you get (per insert) when using NULL versus when
using non-null? If you get the same result as me you'll see 1 and 2
logical index reads respectively, which again is what you would
expect.

I haven't tested the time differences. How did you do so, and did you
ensure there were no strange caching effects etc.?
Jeremy Rickard