perfomance issues with round() and truncnum()

[Former Member]

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!

[Former Member]

I test it on SA10.0.1 dor windos, result is: 36sec with round() 30sec with truncnum() Any ideas?

[MCMartin]

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

[Former Member]

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;

[VolkerBarth]

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...

[former_SQLA_member1694870]

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.

[former_SQLA_member1694874]

@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.

[former_SQLA_member1694870]

@Daz, when I added my previous entry there was no reply or comment option available. Thanks anyway!

[VolkerBarth]

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...

[jan24]

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.