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/

    • 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

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

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

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 175 other followers

%d bloggers like this: