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. PowerBuilder 11.5.1.4566 |
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. |
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. |