Friday 12 February 2010

One more COALESCE vs NVL example to finish the week...

How many times do you think you've ever written something like this
nvl('x',user)

Small cost? What if you executed it all the time.

What if we wrote it as
coalesce('x',user)

No biggy?

Executed on my laptop with tracing on
TRACING SAGE@sw10g> begin
  2  for i in 1..power(2,17) loop
  3    if nvl('x',user) = 'x' then
  4    null;
  5    end if;
  6  end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:08.42
Without tracing it was still about 4 seconds.
TRACING SAGE@sw10g> begin
  2  for i in 1..power(2,17) loop
  3    if coalesce('x',user) = 'x' then
  4    null;
  5    end if;
  6  end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
Why?

Check out the tkprof output...
SELECT USER 
FROM
 SYS.DUAL


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        0      0.00       0.00          0          0          0           0
Execute 131072      2.21       1.04          0          0          0           0
Fetch   131072      2.81       0.53          0          0          0      131072
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total   262144      5.03       1.58          0          0          0      131072
Every reference to USER in your PL/SQL or Forms code executes that statement from dual.

Sometimes we need to consider the little things, and considered using COALESCE instead of NVL.

2 comments:

SydOracle said...

Better still, if you are going to using USER a lot in PL/SQL, copy it to a package level constant (eg g_user) and use that.

Once I even saved a bunch of time by only hitting SYSDATE one and using DBMS_UTILITY.GET_TIME to keep the global variable up-to-date.

Scott Wesley said...

Quite true, particularly of Forms environments.

Another possibility (in Forms) is utilising something like this within a common function.

TO_DATE(NAME_IN('system.current_datetime'), 'DD-MON-YYYY HH24:MI:SS')