Updated: Jul 24, 2020
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.