So here are two simple performance suggestions. The second was an added bonus I realised I could demonstrate simply.
1) Stop using USER
I'm using USER far less frequently anyway, since it has no context in Oracle APEX, but it is still a handy default value for created_by colums, and I'm sure some Forms programmers could add life with a small refactor.
Sven explores this in his excellent post regarding triggers in 12c (spoiler: there is typically no need for a trigger).
I thought Tim had a section in an article similar to this, but I couldn't find it again.
And there's a tweet. There's always a tweet.
I executed the following on a development server, and it took 47, 50, 50 seconds respectively.
declare v_result varchar2(100); begin for i in 1..1000000 loop v_result := user; end loop; end; / 50 secondsReplacing USER with sys_context('userenv','session_user') took an order of magnitude lower at 2.5, 2.4, and 2.4 seconds.
declare v_result varchar2(100); begin for i in 1..1000000 loop v_result := sys_context('userenv','session_user'); end loop; end; / 2.5 secondsIt's no longer context switching between SQL and PL/SQL.
2) Stop using NVL
Instead, consider COALESCE, or other null-related functions.
So consider the previous test, but NVLing both expressions. At 58 seconds, the time taken seems like the sum of both, plus time to evaluate. Connor has more detail on the difference.
declare v_result varchar2(100); begin for i in 1..1000000 loop v_result := nvl(sys_context('userenv','session_user'),user); end loop; end; / 58 secondsBy swapping the NVL with a COALESCE, you utilise a programming concept called 'short circuit evaluation'. Results: 2.5, 3.1, 2.5 seconds.
declare v_result varchar2(100); begin for i in 1..1000000 loop v_result := coalesce(sys_context('userenv','session_user'),user); end loop; end; / 2.5 secondsI'm sure there's the odd "it depends", but if you don't take the performance freebies, what are things going to be like when you tackle to nasty queries?
I've been on the coalesce bandwagon for a few years now. I think it was Jonathan Lewis, maybe Tom Kyte that first pointed out that coalesce stops the evaluation process if the first value is null, that short circuit is huge, especially if your second value requires a fair amount of processing to determine the value (i.e. a function).
ReplyDeleteThis is one of the things I wish Quick SQL did by default, right now when it creates the trigger code for you it uses NVL instead of coalesce.
As of May 14th, 2021, Quick SQL uses COALESCE in the triggers it creates.
ReplyDelete