RE: USEOSTIME and performance

>>>I have been asked to check the performance differences between USEOSTIME >>>on and off. I expected the that there would be a slightly better >>>performance (4-5% according to manual) with USEOSTIME = 0/No. This >>>isn't the case on the tests done, in fact it is exactly the reverse. >>>With USEOSTIME set to 1/Yes it is consistently better by 4-5%. >>> >>>The test script is a simple insert into a temp table, no time information >used. > > >>If the table does not make use of any "time" column, then the impact of >>USEOSTIME would not come into effect. The difference in the time would be >due >>to some other factor, possibably some key pages already in the bufferpool. > >I think each SQL statement which is executed determines the current time when >it is >started in case any stored procedures or the like are called while the >statement is >executing and these refer to CURRENT. So, I don't think it matters whether >the statement >itself explicitly references time - the values of CURRENT has to be >determined anyway. > >I'm not sure why there is the claimed performance degradation, but I suspect >it is o/s >specific. Somewhere out there in Unix-land there is an o/s where the >gettimeofday() or >equivalent sub-second time system call takes a long time. It needn't be true >on all >platforms. The testing supports this hypothesis, but is hardly conclusive. > >I'd trust measurements of performance over the documentation. > >Yours, >Jonathan Leffler (jleffler@visa.com) #include <bother.ms-exchange.h> This is true --- Silly me - I forgot about a 7.1 bug that I had worked on. ;) In older versions, we actually checked the value of "current" on each row, but now do it at the start of the query. However, supposed that we are selecting a function/stored procedure that is using current? The only other place that I can think of where the time of day is really used is in tracing, such as sqlidebug and such. Madison Pruet