to divide or to multiply

warning, this test is cpu intensive, do not try on your productive server

One user on the developpez.net French forums asked today about rewritting a division in a multiplication for tuning. Like select avg(sal)/2 from emp; in select avg(sal)*.5 from emp;.

Well, I had to test this ! I execute 41055 divisions in a plsql loop. To avoid incrementation, I divide by 1.014… and multiply by 0.986… in a way that the result keep the same all over the loop.


SQL>
SQL> var z number
SQL> var y number
SQL> exec :z := power(2,102)*2e-31;
SQL> exec :y := 1e125;
SQL> set timi on
SQL> exec while (:y>1e-125) loop :y:=:y/:z; end loop
Elapsed: 00:00:00.42
SQL> set timi off
SQL> print y

Y
--------------------------------
9.9879215917842541374103299E-126

SQL> exec :z := power(2,-104)*2e31;
SQL> exec :y := 1e125;
SQL> set timi on
SQL> exec while (:y<1e-125) loop :y:=:y*:z; end loop Elapsed: 00:00:00.28 SQL> set timi off
SQL> print y

Y
--------------------------------
9.9879215917842541374103299E-126

It has been difficult to find an example with clear difference and not too weird :twisted:

The operation divide is slower than multiply, probably to catch divide by zero errors...

9 thoughts on “to divide or to multiply

  1. APC

    Laurent

    Are you sure about this? Optimization by wallclock is notoriously flaky. Certainly 9.2 on my laptop the results look a bit different:

    SQL> var z number
    SQL> var y number
    SQL> exec :z := power(2,102)*2e-31;

    PL/SQL procedure successfully completed.

    SQL> exec :y := 1e125;

    PL/SQL procedure successfully completed.

    SQL> set timi on
    SQL> exec while (:y>1e-125) loop :y:=:y/:z; end loop

    PL/SQL procedure successfully completed.

    Elapsed: 00:00:00.03
    SQL> set timi off
    SQL> print y
    Y
    ----------
    9.988E-126

    SQL>
    SQL> exec :z := power(2,-104)*2e31;

    PL/SQL procedure successfully completed.

    SQL> exec :y := 1e125;

    PL/SQL procedure successfully completed.

    SQL> set timi on
    SQL> exec while (:y>1e-125) loop :y:=:y*:z; end loop

    PL/SQL procedure successfully completed.

    Elapsed: 00:00:00.00
    SQL> set timi off
    SQL> print y
    Y
    ----------
    9.988E-126

    SQL>

    I really wouldn’t want to have to live on that difference. And on 9.2 on Solaris division is apparently faster …

    SQL> var z number
    SQL> var y number
    SQL> exec :z := power(2,102)*2e-31;

    PL/SQL procedure successfully completed.

    SQL> exec :y := 1e125;

    PL/SQL procedure successfully completed.

    SQL> set timi on
    SQL> exec while (:y>1e-125) loop :y:=:y/:z; end loop

    PL/SQL procedure successfully completed.

    Elapsed: 00:00:00.04
    SQL> set timi off
    SQL> print y
    Y
    ----------
    9.988E-126

    SQL>
    SQL> exec :z := power(2,-104)*2e31;

    PL/SQL procedure successfully completed.

    SQL> exec :y := 1e125;

    PL/SQL procedure successfully completed.

    SQL> set timi on
    SQL> exec while (:y>1e-125) loop :y:=:y*:z; end loop

    PL/SQL procedure successfully completed.

    Elapsed: 00:00:00.05
    SQL> set timi off
    SQL> print y
    Y
    ----------
    9.988E-126

    SQL>

    Cheers, APC

  2. Sidhu

    Laurent

    Interesting post

    Here are my findings on 10gr2 in my laptop (windows XP)…pretty much in line with you. I ran the same example:

    SQL> var z number
    SQL> var y number
    SQL> exec :z := power(2,102)*2e-31;

    PL/SQL procedure successfully completed.

    SQL> exec :y := 1e125;

    PL/SQL procedure successfully completed.

    SQL> set timi on
    SQL> exec while (:y>1e-125) loop :y:=:y/:z; end loop

    PL/SQL procedure successfully completed.

    Elapsed: 00:00:00.10
    SQL> set timi off
    SQL> print y

    Y
    ———-
    9.988E-126

    SQL> exec :z := power(2,-104)*2e31;

    PL/SQL procedure successfully completed.

    SQL> exec :y := 1e125;

    PL/SQL procedure successfully completed.

    SQL> set timi on
    SQL> exec while (:y>1e-125) loop :y:=:y*:z; end loop

    PL/SQL procedure successfully completed.

    Elapsed: 00:00:00.04
    SQL> set timi off
    SQL> print y

    Y
    ———-
    9.988E-126

    SQL>

    Sidhu

  3. Francois

    Hello,
    I think that this can derive from a single question: How basic (processor operations) steps a division takes inside the processor ? Probably much more than for a multiplication.
    Francois

  4. Eriks

    That’s a bold statement, knowing that oracle runs not only on windows.

    On

    Oracle9i Enterprise Edition Release 9.2.0.7.0 – 64bit Production
    PL/SQL Release 9.2.0.7.0 – Production
    CORE 9.2.0.7.0 Production
    TNS for HPUX: Version 9.2.0.7.0 – Production
    NLSRTL Version 9.2.0.7.0 – Production

    results are the same for division and multiply.

  5. APC

    I was discussing this with some colleagues and the consensus is that it really comes down to chip architecture. Different chips will implement the instructions in different fashions. As it happens I have just read this on the WorseThanFailure site:

    “Stephen writes primarily C code …. for tiny devices called “Nodes” that have a whopping 512K of memory, a 2×20 display, and a RISC processor that can’t divide. Really: their compiler treats “%” and “/” as syntactical sugar that gets replaced with a call to a library function which, in turn, uses a series of bitshifts and subtractions to perform division.”

    Cheers, APC

  6. Sidhu

    Right Andrew

    I too did some RnD on the net. At many places, there are talks about this thing (ie multiplication is faster than division). As you said ultimately it depends upon how the same thing is being done at the resigters’ level.

    So can we conclude that its independent of Oracle ? (I mean its entirely dependent on hardware, no way related with Oracle)

    Sidhu

  7. Sidhu

    BTW if we discuss the reasons for the same…what can be the reasons that multiplication involves lesser processing time than division. One as Laurent said “checking for divide by zero things”. Any other hits…

    Sidhu

  8. Laurent Schneider Post author

    thanks all for your comments… I was sceptic as I saw this tuning hint. By achieving 40% performance I thought it was worth mentioning. Well, since on APC notebook (is it a notebook with solaris/sparc? ) the division is faster, my test is perhaps is meaningless. Or is it due to TextEdit 😉

    It is also dependent from Oracle. Here one more demo with binary_double, which is about 10x faster … the difference is smaller, here on solaris/10.2


    SQL> declare
    2 z number;
    3 y number;
    4 begin
    5 z := power(2,103)*1e-31;
    6 for i in 1..10 loop
    7 y := 1e125;
    8 while (y<1e-125) loop y:=y/z; end loop;
    9 end loop;
    10 end;
    11 /
    Elapsed: 00:00:02.95
    SQL> declare
    2 z number;
    3 y number;
    4 begin
    5 z := power(2,-103)*1e31;
    6 for i in 1..10 loop
    7 y := 1e125;
    8 while (y>1e-125) loop y:=y*z; end loop;
    9 end loop;
    10 end;
    11 /
    Elapsed: 00:00:01.54
    SQL> declare
    2 z binary_double;
    3 y binary_double;
    4 begin
    5 z := power(2,103)*1e-31;
    6 for i in 1..100 loop
    7 y := 1e125;
    8 while (y>1e-125) loop y:=y/z; end loop;
    9 end loop;
    10 end;
    11 /
    Elapsed: 00:00:02.87
    SQL> declare
    2 z binary_double;
    3 y binary_double;
    4 begin
    5 z := power(2,-103)*1e31;
    6 for i in 1..100 loop
    7 y := 1e125;
    8 while (y>1e-125) loop y:=y*z; end loop;
    9 end loop;
    10 end;
    11 /
    Elapsed: 00:00:02.40

  9. Laurent Schneider Post author


    > SQL> exec while (:y>1e-125) loop :y:=:y*:z; end loop
    >
    > PL/SQL procedure successfully completed.
    >
    > Elapsed: 00:00:00.00

    00.00 seems a bit fast, are you sure y was set to 1e125 at the beginning of the test?

Comments are closed.