# perfomance issues with round() and truncnum()

 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! asked 07 Nov '11, 17:19 AlexeyK77 707●6●12●24 accept rate: 8% I test it on SA10.0.1 dor windos, result is: 36sec with round() 30sec with truncnum() Any ideas? (08 Nov '11, 06:08) AlexeyK77

 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 ``` answered 08 Nov '11, 08:39 Martin 8.6k●119●152●237 accept rate: 14%
 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. answered 08 Nov '11, 08:43 Jan24 75●3●4●8 accept rate: 0%
 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; answered 18 Nov '11, 03:37 mikron 91●3●4●12 accept rate: 0% 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. Having said that, I also think it would be interesting as an additional research, to time the call to both functions on values previously stored. (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
 toggle preview community wiki:

By Email:

Markdown Basics

• *italic* or _italic_
• **bold** or __bold__
• 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:

×250

question asked: 07 Nov '11, 17:19

question was seen: 11,537 times

last updated: 20 Nov '11, 06:21