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

asked 01 Dec '09, 19:37

Calvin%20Allen's gravatar image

Calvin Allen
1.5k232638
accept rate: 25%

edited 03 Dec '09, 03:35


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.

permanent link

answered 02 Dec '09, 12:04

Breck%20Carter's gravatar image

Breck Carter
26.9k440613886
accept rate: 21%

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.

permanent link

answered 02 Dec '09, 08:50

Volker%20Barth's gravatar image

Volker Barth
31.4k316458676
accept rate: 32%

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

permanent link

answered 01 Dec '09, 19:48

Zote's gravatar image

Zote
1.7k364050
accept rate: 43%

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.

(01 Dec '09, 20:30) Calvin Allen

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.

(01 Dec '09, 22:29) Justin Willey

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

(01 Dec '09, 22:44) Calvin Allen

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.

permanent link

answered 02 Dec '09, 16:04

Reimer%20Pods's gravatar image

Reimer Pods
4.2k344583
accept rate: 11%

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.

(03 Dec '09, 09:04) Breck Carter

OTOOH is "on the other other hand" :)

(03 Dec '09, 09:05) Breck Carter
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×246
×114
×35

question asked: 01 Dec '09, 19:37

question was seen: 1,328 times

last updated: 03 Dec '09, 03:35