September 4th, 2008, 05:05 PM
| | |  default timestamp value and performance
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!
|
September 4th, 2008, 06:15 PM
| | | Re: default timestamp value and performance
Henry J. wrote: Quote:
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 |
September 4th, 2008, 10:15 PM
| | | Re: default timestamp value and performance
On Sep 4, 1:14*pm, Serge Rielau <srie...@ca.ibm.comwrote: Quote:
Henry J. wrote: Quote:
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? |
September 4th, 2008, 10:25 PM
| | | Re: default timestamp value and performance
On Sep 4, 1:14*pm, Serge Rielau <srie...@ca.ibm.comwrote: Quote:
Henry J. wrote: Quote:
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? |
September 5th, 2008, 12:25 AM
| | | Re: default timestamp value and performance
Henry J. wrote: Quote:
On Sep 4, 1:14 pm, Serge Rielau <srie...@ca.ibm.comwrote: Quote:
>Henry J. wrote: Quote:
>>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 |
September 5th, 2008, 12:25 AM
| | | Re: default timestamp value and performance
Henry J. wrote: Quote:
On Sep 4, 1:14 pm, Serge Rielau <srie...@ca.ibm.comwrote: Quote:
>Henry J. wrote: Quote:
>>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 |
| Thread Tools | Search this Thread | | | |
Posting Rules
| You may not post new threads You may not post replies You may not post attachments You may not edit your posts
HTML code is Off | | |
| | | What is Bytes?
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 205,414 network members.
|
