default timestamp value and performance

[Henry J.]

I know this could be a dumb question, I just want to confirm that it
is faster to execute inserts, if a timestamp field is set to have a
default value, like DEFAULT CURRENT TIMESTAMP, and skip that column in
inserts, than to include explicit timestamps in the inserts and let
the DB insert them.

Thanks!

[Serge Rielau]

Henry J. wrote:

I know this could be a dumb question, I just want to confirm that it
is faster to execute inserts, if a timestamp field is set to have a
default value, like DEFAULT CURRENT TIMESTAMP, and skip that column in
inserts, than to include explicit timestamps in the inserts and let
the DB insert them.

Matter of fact the DEFAULT will be ever so slightly more expensive.
Now before anyone gets excited here the difference is minuscule.
Essentially DB2 needs to "enrich" the INSERT. So it needs to root around
for the default value and plug it in.
This is a compile time cost only. At runtime there is no difference
whatsoever

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

[Henry J.]

On Sep 4, 1:14*pm, Serge Rielau <[e-mail]:

Henry J. wrote:

I know this could be a dumb question, I just want to confirm that it
is faster to execute inserts, if a timestamp field is set to have a
default value, like DEFAULT CURRENT TIMESTAMP, and skip that column in
inserts, than to include explicit timestamps in the inserts and let
the DB insert them.

Matter of fact the DEFAULT will be ever so slightly more expensive.
Now before anyone gets excited here the difference is minuscule.
Essentially DB2 needs to "enrich" the INSERT. So it needs to root around
for the default value and plug it in.
This is a compile time cost only. At runtime there is no difference
whatsoever

If I have a query that inserts 1 million rows. One way is to bind the
timestamp field to the current time in the app; the other is to let DB
insert the current time. For the latter, the current time will
increase during the course of the huge insertion, the timestamp for
the last row inserted would be bigger than that for the first row
inserted. When the app passes the timestamp, it just passes one
value. In light of this, if the DB implments the default timestamp by
"enrich" the insert statement, it appears to me it would be more
expensive in run-time as well.

I thought the DB would implement the default timestamp by some low
level writes, making it faster than the timestamp from the app.
Surprised to know it could be other way around. Is it the same in
other databases?

[Henry J.]

On Sep 4, 1:14*pm, Serge Rielau <[e-mail]:

Henry J. wrote:

I know this could be a dumb question, I just want to confirm that it
is faster to execute inserts, if a timestamp field is set to have a
default value, like DEFAULT CURRENT TIMESTAMP, and skip that column in
inserts, than to include explicit timestamps in the inserts and let
the DB insert them.

Matter of fact the DEFAULT will be ever so slightly more expensive.
Now before anyone gets excited here the difference is minuscule.
Essentially DB2 needs to "enrich" the INSERT. So it needs to root around
for the default value and plug it in.
This is a compile time cost only. At runtime there is no difference
whatsoever

Another one. If a timestamp column has a default value (current
timestamp), will the inserts with expliclit timestamps be even more
expensive?

[Serge Rielau]

Henry J. wrote:

On Sep 4, 1:14 pm, Serge Rielau <[e-mail]:

>Henry J. wrote:

>>I know this could be a dumb question, I just want to confirm that it
is faster to execute inserts, if a timestamp field is set to have a
default value, like DEFAULT CURRENT TIMESTAMP, and skip that column in
inserts, than to include explicit timestamps in the inserts and let
the DB insert them.

Matter of fact the DEFAULT will be ever so slightly more expensive.
Now before anyone gets excited here the difference is minuscule.
Essentially DB2 needs to "enrich" the INSERT. So it needs to root around
for the default value and plug it in.
This is a compile time cost only. At runtime there is no difference
whatsoever

If I have a query that inserts 1 million rows. One way is to bind the
timestamp field to the current time in the app; the other is to let DB
insert the current time. For the latter, the current time will
increase during the course of the huge insertion, the timestamp for
the last row inserted would be bigger than that for the first row
inserted. When the app passes the timestamp, it just passes one
value. In light of this, if the DB implments the default timestamp by
"enrich" the insert statement, it appears to me it would be more
expensive in run-time as well.

I thought the DB would implement the default timestamp by some low
level writes, making it faster than the timestamp from the app.
Surprised to know it could be other way around. Is it the same in
other databases?

CURRENT TIMESTAMP has the same value for the entire statement.
If you want different numbers per row you need to do
TIMESTAMP(generate_unique())

I do not know about other DBMS by design - or ignorance in the case of IDS

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

[Serge Rielau]

Henry J. wrote:

On Sep 4, 1:14 pm, Serge Rielau <[e-mail]:

>Henry J. wrote:

>>I know this could be a dumb question, I just want to confirm that it
is faster to execute inserts, if a timestamp field is set to have a
default value, like DEFAULT CURRENT TIMESTAMP, and skip that column in
inserts, than to include explicit timestamps in the inserts and let
the DB insert them.

Matter of fact the DEFAULT will be ever so slightly more expensive.
Now before anyone gets excited here the difference is minuscule.
Essentially DB2 needs to "enrich" the INSERT. So it needs to root around
for the default value and plug it in.
This is a compile time cost only. At runtime there is no difference
whatsoever

Another one. If a timestamp column has a default value (current
timestamp), will the inserts with expliclit timestamps be even more
expensive?

No. Keep in mind that we are talking about some 100 cycles in a GHz CPU.
So "even more" is a really funny phrase even if that were the case

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