Performance of Apex Conditions

Just a little tip I picked up at the InSync13 conference from listening to Scott Wesley. If you have a lot of conditions that look like this:

apex-condition-plsql-expression(conditions based on a PL/SQL Expression, where the PL/SQL itself doesn’t actually call anything outside of Apex – it’s only dependent on variables that Apex already knows)

Because it’s a PL/SQL expression, the Apex engine must execute this as dynamic PL/SQL – requiring a parse/execute/fetch. This might take maybe 0.03 seconds or so. If there’s only one condition like this on a page, it won’t make any difference. But if there are 50 conditions on a page, it can make a difference to the overall page performance – adding up to 1 whole second or more to the page request, which can be noticeable.

The better alternative is to use the condition type Value of Item / Column in Expression 1 = Expression 2, e.g.:

apex-condition-item-equals-expressionThis condition type requires no dynamic PL/SQL – no parsing – which can reduce the time required to an almost negligible amount.

6 thoughts on “Performance of Apex Conditions

    1. Yes, an Apex condition based on a SQL query will benefit from the normal range of performance tuning principles that apply to any SQL query, including scalar subquery caching.


  1. And for SQL that includes calls to v() function, although usually you can get around it through other means, ie
    where app_id = (select v(‘APP_ID’) from dual)
    is better than
    where app_id = v(‘APP_ID’)
    … but you might as well use
    where app_id = apex_application.g_flow_id


Comments are closed.