|
Hi! lets make test procedure: ALTER PROCEDURE "DBA"."my_test"() no result set begin declare @i integer; declare @j integer; set @i = 1000000; while @i > 0 loop --set @j = truncnum(1000 * rand(),0); set @j = round(1000 * rand(), 0); set @i = @i - 1; end loop; end running this procedure on SA 12 (macosx) with round() I am get execution time about 15sec but this procedure with set @j = truncnum(1000 * rand(),0); running less than 5sec Question: why version with round work so slow? what the difference between round and truncnum? Thank's! |
|
Truncnum ist just cutting off everything after the defined digit. Round in contrast is doing a normal rounding: truncnum 2.26 => 2.2 round 2.26 => 2.3 |
|
Because use a different algorithm. TRUNCNUM only cuts off the extra digits and ROUND evaluates (eg for positive number: 19.53+.5) and then cuts result. |
|
The point there is a big difference in runtime. apart from consideration of functional result, it seems for me also very strange. Another test show that. begin declare @i integer; declare @j integer; set @i = 1000000; while @i > 0 loop -- set @j = round(1000 * rand(), 0); -- it's take about 7 sec. set @j = floor(1000 * rand() + 0.5); -- about 3 sec. produce the same result as the line above set @i = @i - 1; end loop; end; begin declare @i integer; declare @n DOUBLE; declare @r integer; declare @f integer; set @i = 1000000; while @i > 0 loop set @n = 1000 * rand(); set @r = round(@n, 0); set @f = floor(@n + 0.5); if @r != @f then RAISERROR 28000 'difference %1 <> %2', @r, @f end if; set @i = @i - 1; end loop; end; When trying to time functions like round(), I would strongly recommend to not include calls to other functions like rand() in the same test as that may influence the time as well. IMHO, it would be better to fill a local temporary table with random values and then measure the time it takes to round/truncate over the table' contents...
(19 Nov '11, 08:29)
Volker Barth
It seemed to me that the comparison test done is perfectly valid since all the other variables were kept constant.
(19 Nov '11, 11:51)
Derli Marcochi
Replies hidden
@Derli you can reply to specific comments/answers using the 'Reply' or 'Add new comment' options on each Comment/Answer. I've converted this answer to a comment for you.
(19 Nov '11, 12:08)
Daz Liquid
@Daz, when I added my previous entry there was no reply or comment option available. Thanks anyway!
(19 Nov '11, 16:57)
Derli Marcochi
I agree that in general "all other variables were kept constant". However, my point is just that the output of rand() in the sample is not constant as rand() does only generate reproducable results when seeded with the same seed. So I just wanted to mention that the differences in output may also influende the timings of the further functions - and that I'm not sure whether rand() itself returns its result in constant time...
(20 Nov '11, 06:20)
Volker Barth
|
I test it on SA10.0.1 dor windos, result is: 36sec with round() 30sec with truncnum() Any ideas?