Welcome to SQLA Beta

Question

2

1

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

edited Dec 3 at 3:35

asked Dec 1 at 19:37

Calvin Allen
891●1●11

Breck Carter · Accepted Answer · 2009-12-02 12:04:14Z UTC

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.

Volker Barth · Answer 2 · 2009-12-02 08:50:13Z UTC

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.

Zote · Answer 3 · 2009-12-01 19:48:20Z UTC

1

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

answered Dec 1 at 19:48

Zote
821●1●11

	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. – Calvin Allen Dec 1 at 20:30
	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. – Justin Willey Dec 1 at 22:29
	We populate those two columns with what we need, but we don't save automatically. – Calvin Allen Dec 1 at 22:44

Reimer Pods · Answer 4 · 2009-12-02 16:04:55Z UTC

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.

Performance Hit

4 Answers