Revealing the unseen secrets of IF clause.

I hardly remember to have encountered with a subprograms without a conditional clause, are we not widely using the conditional structs in our PL/SQL subprograms. We usually implement much of our logic's using IF statements but were you aware of Optimizing your structs with Short circuit evaluation means that PL/SQL need not evaluate all of the expression in an IF statement thereby saving a lot of the computing overhead.

For example, when evaluating the expression in the following IF statement, PL/SQL stops evaluation and immediately executes the ELSE branch if the first operand is either FALSE or NULL:

IF condition1 AND condition2
THEN
   ...
ELSE
   ...
 END IF; 

How could this be helpful for me while writing codes. You need not ponder much on that as you just need to put the expensive conditions (expensive in terms of CPU or memory utilization) at the END just like the one shown below.

IF low_CPU_condition AND high_CPU_condition
THEN
....
END IF;

The low_CPU_condition is evaluated first, and if the result is enough to determine the end result of the AND operation (i.e., the result is FALSE), the more expensive condition will not be evaluated, and your application’s performance is the better for that evaluation’s not even happening.

Analogy of short circuiting in nested IF

IF low_CPU_condition
THEN
   IF high_CPU_condition
   THEN
      ...
   END IF;
END IF;

Now, high_CPU_condition is evaluated only if low_CPU_condition evaluates to TRUE. This is the same effect as short-circuit evaluation, but it’s more obvious at a glance what’s going on. It’s also more obvious that my intent is to evaluate low_CPU_condition first.

Why not check this out with real examples.

Lets create a Function taking a significant time in execution.

CREATE OR REPLACE FUNCTION test_func (p_number  IN  NUMBER)
  RETURN number AS
  V_NUM NUMBER;
BEGIN
   DBMS_LOCK.sleep(0.5);
    FOR i in 1..100000
      LOOP
        V_NUM := (P_NUMBER + i)* i;
        END LOOP;
     RETURN V_NUM;
END;

Lets Now write some codes which invokes this function based on IF constructs.

DECLARE
  l_loops NUMBER := 10;
  l_start NUMBER;
  l_NUM   NUMBER := 5;
BEGIN

  l_start := DBMS_UTILITY.get_time;

  FOR i IN 1 .. l_loops LOOP
    IF test_func(i) > 10 AND L_NUM <> 5 THEN
    
      NULL;
    
    END IF;
  END LOOP;

  DBMS_OUTPUT.put_line('Normal         : ' ||
                       (DBMS_UTILITY.get_time - l_start));

  -- Time short-circuit 
  l_start := DBMS_UTILITY.get_time;

  FOR i IN 1 .. l_loops LOOP
  
    IF L_NUM <> 5 AND test_func(i) > 10 THEN
      NULL;
    
    END IF;
  END LOOP;
  DBMS_OUTPUT.put_line('Short circuit  : ' ||
                       (DBMS_UTILITY.get_time - l_start));
END;

-----------------------------------------------------------------------
Normal         : 533
Short circuit  : 0

On analyzing the above code we can see in the normal processing of the IF statement (IF test_func(i) > 10 AND L_NUM <> 5 THEN) the second condition(L_NUM <> 5) is FALSE but as we kept it at the end the fist statement which demands a high computation is executed taking a considerable time while in the Short Circuit IF condition (IF L_NUM <> 5 AND test_func(i) > 10). we have placed the less computative section first thereby preventing the overhead in high computative constructs at the end.

It is evident from the above example that IF clause in SC evaluation skyrockets the performance.

#PLSQL, #Short_Circuit_Evaluation, #ORACLE # Oracledevelopment #Oracledatabase #PLSQL_PROGRAMMING #PLSQL_LEARNING #PLSQLdevelopment