MOD vs TRUNC


Try this on your own machine. I was surprised to find that even with the extra code and extra variable, TRUNC seems to outperform MOD when testing loop counters. (Its of course unlikely that the two second gain is going to make any major difference). Elapsed times from several executions are shown

SQL> set timing on
SQL> declare
  2    x number;
  3    y number;
  4  begin
  5    for i in 1 .. 1000000 loop
  6       if mod(i,50000) = 0 then
  7          null;
  8       end if;
  9    end loop;
 10  end;
 11  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:08.87
Elapsed: 00:00:08.88
Elapsed: 00:00:09.06
Elapsed: 00:00:08.85
Elapsed: 00:00:08.90

## TRUNC test

SQL>
SQL> declare
  2    x number;
  3    y number := 0;
  4  begin
  5    for i in 1 .. 1000000 loop
  6       x := trunc(i/50000);
  7       if x > y then
  8          y := x;
  9       end if;
 10    end loop;
 11  end;
 12  /

Elapsed: 00:00:07.98
Elapsed: 00:00:07.78
Elapsed: 00:00:07.75
Elapsed: 00:00:07.79
Elapsed: 00:00:08.02

Update: Thanks to Bruno (and Torben - see below) for this addition. You can of course avoid all the pain of TRUNC and MOD with a simple if-test.

SQL>
SQL> declare
  2    x number := 0;
  3    y number := 0;
  4      begin
  5   for i in 1 .. 1000000 loop
  6        if x = 50000 then
  7           x := 0;
  8        else
  9           x := x+1;
 10        end if;
 11   end loop;
 12  end;
 13  /

Elapsed: 00:00:03.61
Elapsed: 00:00:03.57
Elapsed: 00:00:03.57
Elapsed: 00:00:03.52
Elapsed: 00:00:03.57

Torben from Miracle also added the usefulness of suppressing any data type conversions with the following example:

SQL>
SQL> declare
  2    x number := 0;
  3    y number := 0;
  4      begin
  5   for i in 1 .. 1000000 loop
  6        if x = 50000.0 then
  7           x := 0.0;
  8        else
  9           x := x+1.0;
 10        end if;
 11   end loop;
 12  end;
 13  /

Elapsed: 00:00:02.43
Elapsed: 00:00:02.52
Elapsed: 00:00:02.30
Elapsed: 00:00:02.31
Elapsed: 00:00:02.31

which I then took to its logical conclusion by converting the whole lot to the PLS_INTEGER datatype


SQL>
SQL> declare
  2    x pls_integer := 0;
  3    y pls_integer := 0;
  4    w pls_integer := 1;
  5    z pls_integer := 50000;
  6      begin
  7   for i in 1 .. 1000000 loop
  8        if x =  z then
  9           x := y;
  8        else
  9           x := x+w;
 10        end if;
 11   end loop;
 12  end;
 13  /

Elapsed: 00:00:01.95
Elapsed: 00:00:02.22
Elapsed: 00:00:01.93
Elapsed: 00:00:01.94
Elapsed: 00:00:02.22