APEX conditions and performance

Having recently seen some examples of non-optimal code in APEX conditions (e.g. item rendering conditions and read-only conditions)  I thought it worth writing a few words about them.  By putting them here on my blog I can refer to them in future rather than writing them again. Also I may receive useful feedback from readers to improve or correct my advice.

For a very simple condition such as “when the value of item P1_JOB is SALESMAN” there are many condition types to choose from including:

1) Value of item/column in Expression 1 = Expression 2

Expression 1: P1_JOB

Expression 2: SALESMAN

2) PL/SQL Expression

Expression 1: :P1_JOB = ‘SALESMAN’

3) Exists (SQL query returns at least one row)

SELECT NULL

FROM DUAL

WHERE :P1_JOB = ‘SALESMAN’

(Yes I have actually seen this one!)

4) PL/SQL Function returning Boolean

Begin

  If :P1_JOB = ‘SALESMAN’ then

    Return true;

  Else

    Return false;

  End if;

End;

… and so on.

The correct type to use in this case is (1) because it is the only declarative method; all the others require APEX to run dynamic PL/SQL to evaluate the condition.

To evaluate (1) APEX will do something like this:

IF v(expression1) = expression2 THEN…

To evaluate (2) APEX will do something like this:

-- Parse expression1 to look for item references e.g. :P1_JOB

…

EXECUTE IMMEDIATE expression1 INTO l_result USING v(item_name);

IF l_result THEN …

(In fact it will have to use DBMS_SQL not EXECUTE IMMEDIATE because the number of bind values can vary.  The pseudo-code above is merely an educated guess by the way.)

(3) and (4) will involve similar code to (2).

True, the cost of executing the dynamic PL/SQL won’t be huge, but bear in mind that a page may have many items with conditions like this, and the small penalty is then incurred many times per page load, which adds up particularly on a frequently used page.

Of course, if you need a more complex condition such as “Job is manager and location = London” then you cannot use method (1), you will need to use method (2) (not (3) or (4) please!).  Always look for the simplest and most appropriate condition type for your needs.

Also, if the condition involves a function call e.g. (emp_pkg.is_eligible(:P1_EMPNO) = ‘Y’) and is used in more than one place it is probably better to execute the function call once, assign the result to a hidden page item, and then use a type (1) condition “value of P1_IS_ELIGIBLE is equal to Y”.