We have an application that uses a last_update (timestamp) column on just about every table. We manage this column on the client side, but due to differences in user clocks (always used today() before) we are talking about having the time come from the database.
What kind of performance hit are we talking about if, every time a single column is updated/tabbed off in a form, we grab the db timestamp?
This timestamp has to be visible to the user as soon as they hit insert.
The answer to your actual question, "What kind of performance hit are we talking about if, every time a single column is updated/tabbed off in a form, we grab the db timestamp?" is "very little"... SELECT CURRENT TIMESTAMP INTO :whatever FROM SYS.DUMMY USING SQLCA will not cause the engine any grief whatsoever, no disk I/O ever, nothing difficult at all. You will see the client-server overhead of an ODBC request/response... if you are on a WAN with extreme latency (e.g., NYC to Ireland via the moon) then there will be a time delay. If you are on a fast LAN then response will be snappy. If you are using a local database (dbeng10) then response will be instantaneous.
You are talking about doing this no more often than human beings press keys etcetera... you are not talking about a fast loop doing hundreds of thousands of operations... so the overhead should be minimal.
answered 02 Dec '09, 12:04
SQLA has the particular DEFAULT TIMESTAMP for that - and it's much easier and more performant than a trigger. As to the docs:
The main difference between DEFAULT CURRENT TIMESTAMP and DEFAULT TIMESTAMP is that DEFAULT CURRENT TIMESTAMP is set only at INSERT, while DEFAULT TIMESTAMP is set at both INSERT and UPDATE.
We typically use two columns for tracking the date of creation and last modification, as in
However, it won't get filled until you do an insert/update, and you will have to read the column's value after the insert/update to get the actual value.
As Justin has stated, storing an "all empty" row is usually bad practive w.r.t. row fragmentation. So I'm not sure whether this defaults meet your requirements.
answered 02 Dec '09, 08:50
Why you don't use an trigger to set value?
answered 01 Dec '09, 19:48
If you'd like to reduce the amount of requests to the database for the current time this could be an approach: on startup get the current timestamp from server, but also the local system time, and calculate the difference. Use this as an offset to initialize the timestamp column with the local system time.
Keep track of the last time the database time was fetches. Every once in a while (e.g. every 10 min) recalculate the offset,
OTOH if the overhead of fetching the time from the database is as insignificant as Breck says you might as well keep on using that.
answered 02 Dec '09, 16:04