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-expression

This condition type requires no dynamic PL/SQL – no parsing – which can reduce the time required to an almost negligible amount.

Your ‘PL/SQL Code’
APEX Interactive Report raising javascript error

Comments

  1. I don’t know APEX, so this might be completely off-topic. But is there any chance you might be able to tweak the generated SQL in order take advantage of scalar subquery caching to speed up the PL/SQL parts if they’re executed within a SQL query?

    http://blog.jooq.org/2011/09/02/oracle-scalar-subquery-caching/

    • 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.

  2. 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

Leave a Reply

Your email address will not be published / Required fields are marked *