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's gravatar image

AlexeyK77
70761224
accept rate: 8%

edited 07 Nov '11, 17:21

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
permanent link

answered 08 Nov '11, 08:39

Martin's gravatar image

Martin
8.6k118151237
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.

permanent link

answered 08 Nov '11, 08:43

Jan24's gravatar image

Jan24
75348
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;

permanent link

answered 18 Nov '11, 03:37

mikron's gravatar image

mikron
913412
accept rate: 0%

edited 18 Nov '11, 03:42

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
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • 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:

×246

question asked: 07 Nov '11, 17:19

question was seen: 11,475 times

last updated: 20 Nov '11, 06:21