Last week I revealed the numbers standing behind the overhead of calling a Pl/SQL function from within an SQL statement.

I’ve left two questions open:

– Is it always a performance issue, when you call a PL/SQL function from a SQL statement?

– What can be done to maintain the function encapsulation (have the code DRY) and keep high performance?

Lets take it one by one.

To see if it is always an issue, lets run some tests. We will be running the SQL statements with inlined calculation formula and PL/SQL function call using different SQL result sizes and different number of executions.

For simplicity and clarity. The function i used does not use DETERMINISTIC nor RESULT_CACHE keyword. In the examples, i assume that you run the function on data unique data, so that each function call is unique.

I’ve made those assumptions only to clearly isolate the issue with PL/SQL function call overhead. If the function calls are repeatable, you could benefit from the DETERMINISTIC or RESULT_CACHE, but this is out of scope of the article, so I will not investigate it further now.

In my previous post I compared the simple SQL statement performance with or without PL/SQL function call. The comparison was done on 1,2 million rows. how does it look when we process less rows with our queries? Is it still a pain? Do we need to worry? How much of a pain is it? Let’s check.

I will use the function implementation as before.

CREATE OR REPLACE FUNCTION get_val( val IN INTEGER ) RETURN INTEGER IS BEGIN RETURN CASE mod( val , 2 ) WHEN 1 THEN 6 ELSE 3 END; END; /

The following script should give us some answers

set timing on set feedback off set pagesize 0 PROMPT Calculate using function call from SQL on 100 rows data sets 10000 times DECLARE res INTEGER := 0; BEGIN FOR r IN 1 .. 10000 LOOP SELECT sum(get_val(ROWNUM)) val INTO res FROM dual CONNECT BY LEVEL <= 100; END LOOP; dbms_output.put_line(res); END; / PROMPT Calculate using inline calculation in SQL on 100 rows data sets 10000 times DECLARE res INTEGER := 0; BEGIN FOR r IN 1 .. 10000 LOOP SELECT sum( CASE mod( ROWNUM , 2 ) WHEN 1 THEN 6 ELSE 3 END ) val INTO res FROM dual CONNECT BY LEVEL <= 100; END LOOP; dbms_output.put_line(res); END; / PROMPT Calculate using function call from SQL on 1 row data sets 100000 times DECLARE res INTEGER := 0; BEGIN FOR r IN 1 .. 100000 LOOP SELECT sum(get_val(ROWNUM)) val INTO res FROM dual; END LOOP; dbms_output.put_line(res); END; / PROMPT Calculate using inline calculation in SQL on 1 row data sets 100000 times DECLARE res INTEGER := 0; BEGIN FOR r IN 1 .. 100000 LOOP SELECT sum( CASE mod( ROWNUM , 2 ) WHEN 1 THEN 6 ELSE 3 END ) val INTO res FROM dual; END LOOP; dbms_output.put_line(res); END; /

And the results give us some answers

Calculate using function call from SQL on 100 rows data sets 10000 times Elapsed: 00:00:10.523 Calculate using inline calculation in SQL on 100 rows data sets 10000 times Elapsed: 00:00:01.572 Calculate using function call from SQL on 1 row data sets 100000 times Elapsed: 00:00:05.891 Calculate using inline calculation in SQL on 1 row data sets 100000 times Elapsed: 00:00:03.841

The overhead is there, but it is less visible, when the data volumes that the SQL query processes are small.

I leave it up to you to decide if this is a reason good enough, to use PL/SQL functions in SQL statements.

In my daily work I use the following pattern.

If it is small and is not called frequently, I don’t hesitate to use all the goodies of PL/SQL in SQL.

If it is big or called really often, I avoid this approach as much as possible.

Now, lets go to the second question.

What can be done to maintain the function encapsulation (have the code DRY) and keep high performance?

There are several ways to keep the performance:

– Keep everything inlined in SQL queries

– Encapsulate the calculation with Views

– Encapsulate the calculation using Virtual Column (Oracle 11g and above)

– Use PL/SQL to do the calculation

– Use Dynamic SQL and and PL/SQL functions to build the calculation formula (not to calculate the actual value)

Inlining the calculation can be fine, if you use it only in one SQL statement, if it is 2 or more, and the formula is actually complex, it would be better to encapsulate it somehow.

You could use View over a table to create a facade over the calculation logic that is done purely in SQL.

This approach is OK, as long as the formula is used on one table. If it is two or more table, you end up having the same formula scattered across many views which could turn into maintenance hell if the formula needs to be changed.

The script below will test the performance of the view vs the performance of inline calculation.

set feedback off set pagesize 0 CREATE TABLE t1 AS SELECT rownum AS rn FROM dual CONNECT BY LEVEL < 1200000; CREATE OR REPLACE VIEW v1 AS SELECT CASE mod( ROWNUM , 2 ) WHEN 1 THEN 6 ELSE 3 END val FROM t1; set timing on PROMPT Calculate using inline calculation in SQL SELECT sum( CASE mod( ROWNUM , 2 ) WHEN 1 THEN 6 ELSE 3 END ) FROM t1; PROMPT Calculate using calculation in View SELECT sum(val) from v1; SET TIMING OFF DROP TABLE t1; DROP VIEW v1;

The performance of the view is nearly as good as the performance of the plain SQL query. The view was slightly slower. I have repeated the test couple of times with similar results.

Calculate using inline calculation in SQL 5399997 Elapsed: 00:00:00.660 Calculate using calculation in View 5399997 Elapsed: 00:00:00.731

You could create a table with virtual column to achieve the same goal as with the view. This feature is available since Oracle 11g. Same as with view, if the formula is used on many tables, you will need to have it defined in many places and you may end up having maintenance issues.

set feedback off set pagesize 0 CREATE TABLE t1 AS SELECT rownum AS rn FROM dual CONNECT BY LEVEL < 1200000; CREATE TABLE t2(rn NUMBER, val NUMBER GENERATED ALWAYS AS (CASE mod( rn , 2 ) WHEN 1 THEN 6 ELSE 3 END) VIRTUAL); INSERT INTO t2 (rn) SELECT rn from t1; COMMIT; set timing on PROMPT Calculate using inline calculation in SQL SELECT sum( CASE mod( ROWNUM , 2 ) WHEN 1 THEN 6 ELSE 3 END ) FROM t1; PROMPT Calculate using calculation ON virtual column SELECT sum( val ) FROM t2; SET TIMING OFF DROP TABLE t1; DROP TABLE t2;

Interesting thing with virtual column, is that the calculation was actually executed faster than with a regular query.

I’ve tested it a couple of times and the results were always in favour of calculation on virtual column.

Calculate using inline calculation in SQL 5399997 Elapsed: 00:00:00.687 Calculate using calculation ON virtual column 5399997 Elapsed: 00:00:00.444

The solutions that allow you to have the business logic encapsulated in single place and avoid the performance trade-off are:

– Use PL/SQL to do the work and bulk collect the data.

– Use Dynamic SQL and function

set feedback off set serveroutput on set pagesize 0 CREATE TABLE t1 AS SELECT rownum AS rn FROM dual CONNECT BY LEVEL <= 1200000; CREATE OR REPLACE FUNCTION get_val(val IN INTEGER) RETURN INTEGER IS BEGIN RETURN CASE mod( val , 2 ) WHEN 1 THEN 6 ELSE 3 END; END; / CREATE OR REPLACE FUNCTION get_val_str(col_nm VARCHAR2) RETURN VARCHAR2 IS BEGIN RETURN 'case mod('||col_nm||',2) when 1 then 6 else 3 end'; END; / set timing on PROMPT Calculate using function call in SQL SELECT sum( get_val(rn) ) val FROM t1; PROMPT Calculate using PL/SQL with bulk collect DECLARE TYPE t_num_tab IS TABLE OF INTEGER; val_lst t_num_tab; res INTEGER := 0; BEGIN SELECT rn BULK COLLECT INTO val_lst FROM t1; FOR i IN val_lst.FIRST .. val_lst.LAST loop res := res + get_val(val_lst(i)); END loop; dbms_output.put_line(res); END; / PROMPT Calculate using dynamic SQL with with function call for inlining calculation DECLARE res INTEGER; BEGIN EXECUTE IMMEDIATE 'SELECT sum('||get_val_str('rn')||') val FROM t1' INTO res; dbms_output.put_line(res); END; / PROMPT Calculate using inline calculation in SQL SELECT sum( CASE mod( rn , 2 ) WHEN 1 THEN 6 ELSE 3 END ) val FROM t1; set timing off DROP FUNCTION get_val; DROP TABLE t1;

Calculate using function call in SQL 5400000 Elapsed: 00:00:13.769 Calculate using PL/SQL with bulk collect Elapsed: 00:00:01.759 5400000 Calculate using dynamic SQL with with function call for inlining calculation Elapsed: 00:00:00.447 5400000 Calculate using inline calculation in SQL 5400000 Elapsed: 00:00:00.442

The approach witch Native Dynamic SQL has no visible performance impact.

The PL/SQL processing with bulk collect has a noticeable overhead. It is 4 times slower than the pure SQL/native dynamic SQL.

It is however still 8 times faster than the PL/SQL function call from within SQL.

There are many options to further investigate different variants and aspects of each approach.

I’ll leave it up to you to decide which one is your favourite and suits your needs best.