Performance Hit

[Former Member]

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
SQL Anywhere 10.0.1.3629

[Breck_Carter · Accepted solution]

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.

[VolkerBarth]

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

dtCreated        timestamp default current timestamp,
dtLastChanged    timestamp default timestamp,

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.

[reimer_pods]

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.

[Breck_Carter]

I like it!

But... there's always a but, isn't there?... if the local time is truly unreliable, subject to random and whimsical changes, then even the calculation of "once in a while" might be difficult; i.e., how do you really know it's been 10 minutes? One sanity check that comes to mind: if the new calculated time is earlier than the previous calculated time then something funky just happened and the server time must be re-fetched. Nothing to do about the other direction. OTOOH it's probably moot... the local time is probably just offset, not fluctuating.

[Breck_Carter]

OTOOH is "on the other other hand" 🙂

[Former Member]

Why you don't use an trigger to set value?

[Former Member]

We need the value to be visible in the application before anything gets saved. So a new row would have all empty fields EXCEPT for our 'user_id' and 'last_update' fields.

[justin_willey]

How much data then gets stored in the row? I'm sure I've read advice before suggesting that it's a bad idea to create sparsely populated rows and then add the data later, because of fragmentation.

[Former Member]

We populate those two columns with what we need, but we don't save automatically.