dshapiro@apple.com (02/12/91)
Here is a summary of responses I received after asking the comp.databases.question - Can you use the timestamp or date type as a key to a table. I was suprised that one person actually uses this strategy, but I am not thinking about implementing this for any projects, rather I wanted the net's opinion on technical and theoretical advantages/bottlenecks. Basically, it's not a good idea. First let me say I erred in using the term timestamp and Sybase in the same sentence. I meant using a date value - with as much granularity as possible - and not the Sybase implementation of timestamp datatype as the table key. Also, I was thinking of an implementation that grabs the system time and not a table that has the system time on it. I claimed: >Guaranteed monotonically increasing unique number (date) Someone pointed out that the system time is not as fine as one would need: >the rows may not be written in the same >millisecond, the clock reads certainly can happen within the same >tick. This is a problem since the clock read and the disk write may >be seprated by many milliseconds, unless sybase allows you to lock >down your process so >that nothing else can run between the clock >read and the disk write Also >Not so. The question is: can I get a datetime and then be scheduled >out and have someone else request a datetime within 1 msec. The >answer is probably yes, but it will be extremely rare. As an aside, can this be done? I also claimed >Zero wait time for a new key for a table But the call for a the system date/time requires a context switch which I suppose could be an expensive OS operation. On the drawbacks I noted >Uniqueness depends on the granularity of your timestamp function. To which someonce confirmed: >... On many systems the system time >call has a granularity of 20msec or more, and there is a chance of >getting duplicates. I also said > Possible duplication if someone screws with the system time. > (no one can be using the system during the daylight savings time adjustment) However: >Just use GMT and you don't have to worry about that... I then claim: > Portability to new platform could require some time manipulations. But >We just used the long integer and dumped the UNIX time in there.. I'm not sure how well this method would work on other platforms/OS - sort of casting the time/date into a longint. (However, most systems I've seen use this method) Also I neglected the issue of distributed applications which was brought up: >... I can >pretty much guarantee that you will never synchronize multiple clocks >so that duplicate keys can be avoided I was going to run some tests to test multiuser updates using date/time as a key but after a couple of single user runs, I could not get the granularity past 100th of a second using Sybase and a VAX 8810 at which point I knew there would be conflicts without at least a millisecond granularity. I'm still wondering if it's possible to squeeze finer time divisions than even the ms? In any event, thanks to all who replyed personally and through the net. It's good to hear from so many people with creative / constructive comments. Dave Shapiro Apple Computer, Inc. dshapiro@apple.com ------------ The usual disclaimers....