Skip to content

Performance of Apex Conditions

November 14, 2013

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.

About these ads

From → APEX, Oracle

6 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/

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

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

  4. related post here http://www.grassroots-oracle.com/2013/05/performance-of-apex-conditions.html

  5. Reblogged this on Sutoprise Avenue, A SutoCom Source.

  6. I should have known you’d already blogged this tip!

Comments are closed.

Follow

Get every new post delivered to your Inbox.

Join 215 other followers

%d bloggers like this: