Oracle PL/SQL speed of NVL/LENGTH/TRIM calls versus IS NOT NULL AND != ' '

Question

I try to find the best way to check if a CHAR/VARCHAR2 variable contains characters (NULL or spaces should be considered the same, as "no-value"):

I know there are several solutions, but it appears that (NVL(LENGTH(TRIM(v)),0) > 0) is faster than (v IS NOT NULL AND v != ' ')

Any idea why? Or did I do something wrong in my test code?

Tested with Oracle 18c on Linux, UTF-8 db charset ...

I get the following results:

time:+000000000 00:00:03.582731000

time:+000000000 00:00:02.494980000

set serveroutput on;

create or replace procedure test1
is
    ts timestamp(3);
    x integer;
    y integer;
    v char(500);
    --v varchar2(500);
    begin
    ts := systimestamp;
    --v := null;
    v := 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa';
    for x in 1..50000000
    loop
        if v is not null and v != ' ' then
           y := x;
        end if;
    end loop;
    dbms_output.put_line('time:' || (systimestamp - ts) ) ;
    end;
/

create or replace procedure test2
is
    ts timestamp(3);
    x integer;
    y integer;
    v char(500);
    --v varchar2(500);
    begin
    ts := systimestamp;
    --v := null;
    v := 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa';
    for x in 1..50000000
    loop
        if nvl(length(trim(v)),0) > 0 then
           y := x;
        end if;
    end loop;
    dbms_output.put_line('time:' || (systimestamp - ts) ) ;
    end;
/

begin
    test1();
    test2();
end;
/

drop procedure test1;
drop procedure test2;

quit;

What is the purpose of the `nvl` in test2? Unless I am missing something it isn’t doing anything useful. — William Robertson, Sep 30 '19 at 14:49
NVL() is there in case if v is NULL: When v is NULL, TRIM(v) evaluates to NULL and LENGTH() of NULL also evaluates to NULL... I would have expected that LENGTH(NULL) returns zero... I guess each DB engine has its own semantics in such case... — Seb, Sep 30 '19 at 18:18
Sounds like a case of premature optimization also have you run your test over a couple hundred trials. You are running 50million iterations to find a difference of approximately 1.1 seconds or about 2.18*10^-8 per iteration. Is thin test the only thing running on the server, the difference could result from another process being in control of the CPU. — Belayer, Oct 01 '19 at 18:56
Test is done on my own computer (linux box) and nothing else is running. I have executed this code about 30 times over the last days and the results are the same. Just tried again 5 times by making sure nothing else is running and I get similar results. Beside the strange time diff, most important question is what is the best practice to check that a char or varchar2 variable contains characters, considering the blanks are "no-value". — Seb, Oct 02 '19 at 07:14

score 3 · Accepted Answer · answered Oct 08 '19 at 01:51

The best practice is to ignore the speed difference between small functions and use whatever is easiest.

In realistic database programming, the time to run functions like NVL or IS NOT NULL is completely irrelevant compared to the time needed to read data from disk or the time needed to join data. If one function saves 1 seconds per 50 million rows, nobody will notice. Whereas if a SQL statement reads 50 million rows with a full table scan instead of using an index, or vice-versa, that could completely break an application.

It's unusual to care about these kinds of problems in a database. (But not impossible - if you have a specific use case, then please add it to the question.) If you really need optimal procedural code you may want to look into writing an external procedure in Java or C.

Oracle PL/SQL speed of NVL/LENGTH/TRIM calls versus IS NOT NULL AND != ' '

1 Answers1