I'm trying to find if there is any performance diference between
explicitly using a sequence in the insert statement to generate values
for a column and doing this in an insert trigger.

I noticed that th eaccess plan for the 2 situations is quite
different. For the case where the trigger is in place, the optimizer
applies 2 extra residual predicates.
Can anybody explain where the differences come from? Also, for real
life situations, should there be any performance differences between
the 2 approaches? How about oracle?


thanks

create table t(a int, b int);
create sequence s start with 1;

use db2expln to explain
insert into t(nextval for s, 1)

Statement:

insert into t(a, b)values (nextval
for s, 1)


Section Code Page = 1208

Estimated Cost = 7.573070
Estimated Cardinality = 1.000000

Insert: Table Name = DB2ADMIN.T ID = 4,524

End of section


Optimizer Plan:

INSERT
( 2)
|
Table:
DB2ADMIN
T



TRIGGER VERSION:
CREATE TRIGGER TR
NO CASCADE
BEFORE INSERT
ON T
REFERENCING NEW AS newrow
FOR EACH ROW MODE DB2SQL
begin atomic
set newrow.a=nextval for s;
END@

explain


Statement:

insert into t(b)values ( 1)


Section Code Page = 1208

Estimated Cost = 7.574730
Estimated Cardinality = 1.000000

Table Constructor
| 1-Row(s)
Residual Predicate(s)
| #Predicates = 1
Residual Predicate(s)
| #Predicates = 1
Insert: Table Name = DB2ADMIN.T ID = 4,524

End of section


Optimizer Plan:

INSERT
( 2)
/ \
FILTER Table:
( 3) DB2ADMIN
| T
FILTER
( 4)
|
TBSCAN
( 5)
|
TFunc:
SYSIBM
GENROW

filip1150@gmail.com wrote:
> I'm trying to find if there is any performance diference between
> explicitly using a sequence in the insert statement to generate values
> for a column and doing this in an insert trigger.
>
> I noticed that th eaccess plan for the 2 situations is quite
> different. For the case where the trigger is in place, the optimizer
> applies 2 extra residual predicates.
> Can anybody explain where the differences come from? Also, for real
> life situations, should there be any performance differences between
> the 2 approaches? How about oracle?
>
>
> thanks
>
> create table t(a int, b int);
> create sequence s start with 1;
>
> use db2expln to explain
> insert into t(nextval for s, 1)
>
> Statement:
>
> insert into t(a, b)values (nextval
> for s, 1)
>
>
> Section Code Page = 1208
>
> Estimated Cost = 7.573070
> Estimated Cardinality = 1.000000
>
> Insert: Table Name = DB2ADMIN.T ID = 4,524
>
> End of section
>
>
> Optimizer Plan:
>
> INSERT
> ( 2)
> |
> Table:
> DB2ADMIN
> T
>
>
>
> TRIGGER VERSION:
> CREATE TRIGGER TR
> NO CASCADE
> BEFORE INSERT
> ON T
> REFERENCING NEW AS newrow
> FOR EACH ROW MODE DB2SQL
> begin atomic
> set newrow.a=nextval for s;
> END@
>
> explain
>
>
> Statement:
>
> insert into t(b)values ( 1)
>
>
> Section Code Page = 1208
>
> Estimated Cost = 7.574730
> Estimated Cardinality = 1.000000
>
> Table Constructor
> | 1-Row(s)
> Residual Predicate(s)
> | #Predicates = 1
> Residual Predicate(s)
> | #Predicates = 1
> Insert: Table Name = DB2ADMIN.T ID = 4,524
>
> End of section
>
>
> Optimizer Plan:
>
> INSERT
> ( 2)
> / \
> FILTER Table:
> ( 3) DB2ADMIN
> | T
> FILTER
> ( 4)
> |
> TBSCAN
> ( 5)
> |
> TFunc:
> SYSIBM
> GENROW
>
This will be a negligible difference. Just for fun try this trigger
(without BEGIN.. END):
CREATE TRIGGER TR
NO CASCADE
BEFORE INSERT
ON T
REFERENCING NEW AS newrow
FOR EACH ROW MODE DB2SQL
set newrow.a=nextval for s@

I'm actually surprised that the raw INSERT shows so dense. Must be some
OLTP single row optimization.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab