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:
(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.:
This condition type requires no dynamic PL/SQL – no parsing – which can reduce the time required to an almost negligible amount.
About Jeffrey Kemp
Application Designer & Developer at Oracle specialising in Oracle APEX (Application Express), Oracle SQL and PL/SQL. Oracle ACE Alumni ♠️. Piano player, father, husband, Christian. The views expressed on this blog are my own and do not necessarily reflect the views of Oracle.
lukaseder
14 November 2013 - 7:42 pm
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/
Jeffrey Kemp
14 November 2013 - 8:06 pm
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.
Scott
15 November 2013 - 1:28 pm
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
Scott
15 November 2013 - 4:08 pm
related post here http://www.grassroots-oracle.com/2013/05/performance-of-apex-conditions.html
Jeffrey Kemp
16 November 2013 - 7:55 am
I should have known you’d already blogged this tip!