-- Sample Script to test performance improvement from PL/SQL native compilation


-- Computational intensive algorithm with no database access

-- Consider the task of finding all right-angled triangles with all side lengths
-- integer (a. k. a. perfect triangles). We must count only unique triangles,

-- i. e. those whose sides are not each the same integral multiple of the
-- sides of a perfect triangle already found. The following implements an exhaustive
-- search among candidate triangles with all possible combinations of lengths of
-- the two shorter sides, each in the range 1 to a specified maximum. Each candidate
-- is coarsely filtered by testing if the square root of the sum of the squares
-- of the two short sides is within 0.01 of an integer. Triangles which pass this

-- test are tested exactly by applying Pythagoras's theorem using integer arithmetic.
-- Candidate perfect triangles are tested against the list of multiples of perfect
-- triangles found so far. Each new unique perfect triangle is stored in a PL/
-- SQL table, and its multiples (up to the maximum length) are stored in a separate
-- PL/ SQL table to facilitate uniqueness testing. 

-- The implementation thus involves a doubly nested loop with these steps at its

-- heart: several arithmetic operations, casts and comparisons; calls to procedures
-- implementing comparisons driven by iteration through a PL/ SQL table (with yet
-- more arithmetic operations); and extension of PL/ SQL tables where appropriate.
  
-- The elapsed time was measured for p_ max= 5000 (i. e. 12.5 million
-- repetitions of the heart of the loop) using interpreted and natively compiled
-- versions of the procedure. The times were 548 sec and 366 sec respectively (on
-- a Sun Ultra60 with no load apart from the test). Thus the natively compiled
-- version was about 33% faster.

  
-- Connect programmer/p@9i
set serveroutput on

drop procedure Perfect_Triangles;
  
create or replace procedure Perfect_Triangles ( p_max in integer ) is
  t1 integer; t2 integer;
  long integer; short integer; hyp number; ihyp integer;



  type side_r is record ( short integer, long integer );
  type sides_t is table of side_r index by binary_integer;
  unique_sides sides_t; n integer:=0 /* curr max elements in unique_sides */;
  dup_sides sides_t; m integer:=0 /* curr max elements in dup_sides */;

  procedure Store_Dup_Sides ( p_long in integer, p_short in integer ) is
    mult integer:=2; long_mult integer:=p_long*2; short_mult integer:=p_short*2;
  begin
    while ( long_mult < p_max ) or ( short_mult < p_max )

    loop
      n := n+1;
      dup_sides(n).long := long_mult; dup_sides(n).short := short_mult;
      mult := mult+1; long_mult := p_long*mult; short_mult := p_short*mult;
    end loop;
  end Store_Dup_Sides;

  function Sides_Are_Unique ( p_long in integer, p_short in integer )
    return boolean is
  begin
    for j in 1..n

    loop
      if ( p_long = dup_sides(j).long ) and ( p_short = dup_sides(j).short )then
        return false;
      end if;
    end loop;
    return true;
  end Sides_Are_Unique;

begin /* Perfect_Triangles */
  t1 := Dbms_Utility.Get_Time;
  for long in 1..p_max
  loop

    for short in 1..long
    loop
      hyp := Sqrt ( long*long + short*short ); ihyp := Floor(hyp);
      if hyp-ihyp < 0.01 then
        if ( ihyp*ihyp = long*long + short*short )then
          if Sides_Are_Unique ( long, short )then
            m := m+1;
            unique_sides(m).long := long; unique_sides(m).short := short;
            Store_Dup_Sides ( long, short );
          end if;
        end if;
      end if;
    end loop;

  end loop;
  t2 := Dbms_Utility.Get_Time;
  Dbms_Output.Put_Line (
   chr(10) || To_Char( ((t2-t1)/100), '9999.9' ) || ' sec' );
end Perfect_Triangles;
/
Show Errors