SEO

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,

"collincm" <collincm@thathotmail.comservice> wrote in message
news:vssp4m9o09tk91@corp.supernews.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)

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

"Mark A" <ma@switchboard.net> wrote in message
newsWuzb.901$Jf4.93465@news.uswest.net...
> "collincm" <collincm@thathotmail.comservice> wrote in message
> news:vssp4m9o09tk91@corp.supernews.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" <collincm@thathotmail.comservice> wrote in message
news:vsta10gfikrf50@corp.supernews.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?

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:ACyzb.945$Jf4.128406@news.uswest.net...
> "collincm" <collincm@thathotmail.comservice> wrote in message
> news:vsta10gfikrf50@corp.supernews.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" <collincm@thathotmail.comservice> wrote in message
news:vstdu8q6utis54@corp.supernews.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.systables) for some tables in the sample
database and got the exact same numbers on the explain. Don't know why it is
doing that.

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

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_tiredofspam@yahoo.com> wrote in message
news:46e627da.0312040702.6fa97f94@posting.google.c om...
> 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" <collincm@thathotmail.comservice> wrote in message news:<vsumadsrpfks9b@corp.supernews.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)

"collincm" <collincm@thathotmail.comservice> wrote in message news:<vsumadsrpfks9b@corp.supernews.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_tiredofspam@yahoo.com> wrote in message
> news:46e627da.0312040702.6fa97f94@posting.google.c om...
> > 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