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”

APC June 18, 2007 at 16:58

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
Sidhu June 18, 2007 at 18:27

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
Francois June 19, 2007 at 06:39

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
Eriks June 19, 2007 at 07:06

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.
APC June 19, 2007 at 08:51

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
Sidhu June 19, 2007 at 10:14

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
Sidhu June 19, 2007 at 10:16

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
Laurent Schneider Post authorJune 19, 2007 at 11:41

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
Laurent Schneider Post authorJune 21, 2007 at 13:17

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