Hi! lets make test procedure:
ALTER PROCEDURE "DBA"."my_test"()
no result set
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;
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?
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
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
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;
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;