I recently published a post about 15 tips to boosting your Oracle APEX Performance.
While I was pretty good at explaining most of them, there WAS one I rather lazily included in my list with little to no justification.
Although we all generally agree it’s a good PRACTICE, one person, let’s just call him Juergen for fun, wasn’t convinced it could also help with performance.
Apparently ‘Because Michelle says so’ is not enough. Who knew?
I’ll be the first to admit I did not have an immediate answer. I have been accumulating notes on performance hacks from various blog posts and conferences over the years, and this was jotted down amongst them.
But it was the Twittersphere to the rescue, and our awesome #orclapex community came through to teach us all a thing or two. Here is what we (ok, *I*) learned.
1. It can reduce the amount of interpreted code
Scott Wesley was the first to jump in and mention that by removing styling from your query, you were reducing network traffic.
In their projects, they have seen noticeable gains by putting complex queries into views.
When slightly challenged on this by, Scott brilliantly replied:
2. Hard parsing
Jorge Rimblas reminded (ok, taught) us (me) that
“Entry into the SQL Area is expensive. It requires hard parsing. In fact, it’s the most expensive operation that Oracle does when processing SQL statements. See https://connor-mcdonald.com/2016/06/09/parsing-no-big-deal-eh/… for details”
Jorge Rimblas @rimblas
We acknowledged that while this was unlikely to be significant on a single, simple statement with few users, when scaling up to many complex queries across an entire application, the cost of hard parsing into a fixed size SQL Area could have an impact.
Our man Juergen was not yet convinced, but said he was happy to learn. I’m with you!
3. Sorting and searching
Another point was raised concerning sorting and searching:
“It still is about performance, you have to perform extra concatenation in the query if you include HTML, your sorting/searching perf is affected if the column is included in the sort/search, extra memory is used, the list goes on”
Matt Nolan @Matt_FOEX
4. Escaping special characters –> performance implication
If you’re embedding HTML in your queries, you’re going to need to set the Escape Special Characters attribute to ‘No’. Which means you will want to use apex_escape.html on any of your columns in order to mitigate the risks of XSS.
Daniel made the great point below:
Normally you should use select ‘a’||apex_escape.html(my_col)||’b’ from table…It’s more secure and if you use a function in SQL it also has a performance impact!
Daniel Hochleitner @Dani3lSun
Patrick Wolf (@patrickwolf) reminded us that calling apex_escape means SQL to PL/SQL context switch overhead.
His multi-thread response was finally the one to convince Juergen (a.k.a The Doubter) and, we hope, any others.
Apparently, this summary sealed the deal and was enough to let Juergen allow me to keep my Tip 9 of 15. Yay!
5. Scientific proof
When Sven Weller (@SvenWOracle) suggested I create a test case proving the performance impact, Scott ‘volunteered’, and published his results here. Thanks Scott!
Jorge said it well:
Luckily, this is not a religion where you need to “believe”. It’s something you can measure
Jorge Rimblas @rimblas
The thread and conversation was great, and we had other comments from @askMaxSolutions and @AndrewSayer_ as well. Our awesome #orclapex community just keeps coming through.
Juergen, thanks again for the challenge, and for keeping me honest. :-)
APEXionately yours,
Michelle
