Comparing two values


On a recent google discussion (here), it was debated about an efficient means of comparing whether two values are the "same", where null-ness was relevant. The question was whether you could improve upon

if  ( variable1 != variable2 ) or
    ( variable1 is null and variable2 is not null ) or
    ( variable1 is not null and variable2 is null ) then
       <<variables differ>>
else
       <<variables are the 'same'>>
end if;


It was suggested that as long as you have a known value that both variable1 and variable2 will never acquire (lets say -1 in the example above), then the comparison can be simplified with use of NVL

if  nvl(variable1,-1) != nvl(variable2,-1) then
       <<variables differ>>
else
       <<variables are the 'same'>>
end if;

Certainly a simpler solution, and given that PL/SQL is an interpreted language, the shorter code should possibly perform better. But interestingly there are nasty performance overheads with NVL. Procedure P1 below compares a date, a char and a number with the more verbose method, whilst procedures P2 and P3 do it via NVL. P3 assigns the "never possible" values to variables to see if there are any costs to continually evaluating CHR(0) and TO_DATE.

create or replace
procedure P1 ( a number, b number, c date, d date, e varchar2, f varchar2) is
  x number;
begin
  if a != b or ( a is null and b is not null ) or 
     ( a is not null and b is null ) then x := 1; end if;
  if c != d or ( c is null and d is not null ) or 
     ( c is not null and d is null ) then x := 1; end if;
  if e != f or ( e is null and f is not null ) or 
     ( e is not null and f is null ) then x := 1; end if;
end;
/
create or replace
procedure P2 ( a number, b number, c date, d date, e varchar2, f varchar2) is
  x number;
begin
  if nvl(a,-1) != nvl(b,-1) then x := 1; end if;
  if nvl(c,to_date('0001','yyyy')) != nvl(d,to_date('0001','yyyy')) then x := 1; end if;
  if nvl(e,chr(0)) != nvl(f,chr(0)) then x := 1; end if;
end;
/
create or replace
procedure P3 ( a number, b number, c date, d date, e varchar2, f varchar2) is
  x number;  d1 date := to_date('0001','yyyy');  c0 varchar2(1) := chr(0);
begin
  if nvl(a,-1) != nvl(b,-1) then x := 1; end if;
  if nvl(c,d1) != nvl(d,d1) then x := 1; end if;
  if nvl(e,c0) != nvl(f,c0) then x := 1; end if;
end;
/

Then we test a million iterations of the various procedures

SQL> set timing on
SQL> declare
  2    d date := sysdate;
  3    v varchar2(10) := '123123';
  4  begin
  5  for i in 1 .. 1000000 loop
  6    p1(i,i,d,d,v,v);
  7  end loop;
  8  end;
  9  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:04.05
SQL> /

PL/SQL procedure successfully completed.

Elapsed: 00:00:04.04
SQL> declare
  2    d date := sysdate;
  3    v varchar2(10) := '123123';
  4  begin
  5  for i in 1 .. 1000000 loop
  6    p2(i,i,d,d,v,v);
  7  end loop;
  8  end;
  9  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:42.03
SQL> /

PL/SQL procedure successfully completed.

Elapsed: 00:00:41.07
SQL> declare
  2    d date := sysdate;
  3    v varchar2(10) := '123123';
  4  begin
  5  for i in 1 .. 1000000 loop
  6    p3(i,i,d,d,v,v);
  7  end loop;
  8  end;
  9  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:34.08
SQL> /

PL/SQL procedure successfully completed.

Elapsed: 00:00:34.05
SQL>

Ouch! Stay with the verbose version.

Update

Some further examples from Nuno Soutu

Have a look at the results I get with NVL, IS NULL and COALESCE 
(of all things!).  I'd say NVL is well and truly broken - as far 
as performance goes.  9ir2 here.

SQL> create or replace
  2  procedure P1 ( a number, b number, c date, d date, e varchar2, f varchar2) is
  3    x number;
  4  begin
  5    if a != b or ( a is null and b is not null ) or
  6      ( a is not null and b is null ) then x := 1; end if;
  7    if c != d or ( c is null and d is not null ) or
  8      ( c is not null and d is null ) then x := 1; end if;
  9    if e != f or ( e is null and f is not null ) or
 10      ( e is not null and f is null ) then x := 1; end if;
 11  end;
 12  /

Procedure created.

Elapsed: 00:00:00.08
SQL> create or replace
  2  procedure P2 ( a number, b number, c date, d date, e varchar2, f varchar2) is
  3    x number;
  4  begin
  5    if nvl(a,-1) != nvl(b,-1) then x := 1; end if;
  6    if nvl(c,to_date('0001','yyyy')) != nvl(d,to_date('0001','yyyy')) then x := 1; end if;
  7    if nvl(e,chr(0)) != nvl(f,chr(0)) then x := 1; end if;
  8  end;
  9  /

Procedure created.

Elapsed: 00:00:00.07
SQL> create or replace
  2  procedure P3 ( a number, b number, c date, d date, e varchar2, f varchar2) is
  3    x number;
  4  begin
  5    if (coalesce(a,-1) != coalesce(b,-2)) then x:=1; end if;
  6    if (coalesce(c,sysdate+1) != coalesce (d,sysdate+2)) then x:=1; end if;
  7    if (coalesce(e,'a') != coalesce(f,'b')) then x:=1; end if;
  8  end;
  9  /

Procedure created.

Elapsed: 00:00:00.06
SQL>
SQL> set timing on
SQL> declare
  2  d date := sysdate;
  3  v varchar2(10) := '123123';
  4  begin
  5    for i in 1 .. 1000000 loop
  6    p1(i,i,d,d,v,v);
  7    end loop;
  8  end;
  9  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:05.31
SQL> declare
  2  d date := sysdate;
  3  v varchar2(10) := '123123';
  4  begin
  5    for i in 1 .. 1000000 loop
  6    p2(i,i,d,d,v,v);
  7    end loop;
  8  end;
  9  /

PL/SQL procedure successfully completed.

Elapsed: 00:01:37.68
SQL> declare
  2  d date := sysdate;
  3  v varchar2(10) := '123123';
  4  begin
  5    for i in 1 .. 1000000 loop
  6    p3(i,i,d,d,v,v);
  7    end loop;
  8  end;
  9  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:27.81