Some time ago I wrote an article that described the performance tradeoffs one encounters when using SQL user-defined functions – sometimes called UDFs or PSM (Persistent Stored Module) functions – from within an SQL query. In that article, I wrote:
A recent email exchange with software consultant Breck Carter regarding the performance of user-defined functions in SQL has prompted me to re-post this article that I originally wrote in 2008. User-defined functions in SQL have their uses; unfortunately their performance characteristics are highly variable because it is
very difficultimpossible for a query optimizer to take their execution characteristics into account when deciding on the best access plan for a given query. For example, a change to the join order lower down in an execution plan may completely alter the ordering of rows in an intermediate result. If a user-defined function sits above the join, then the execution characteristics of the UDF may be negatively impacted, particularly if the ordering of the rows negates any benefit of memoization – the practice of caching prior execution results of the UDF for subsequent re-use, rather than naively re-executing the function each time. The re-execution of a user-defined function in SQL is prohibitively expensive due to the creation and tear-down of the UDF’s execution context (essentially the same as when executing a stored procedure), which destroys memory and CPU cache locality when executing the query plan.
Anticipating the performance of such complex queries in a production environment can be exceedingly difficult because the performance of the UDF may be affected not only by the size of the underlying tables but also on the amount of correlation between the values referenced within the function, which impacts the benefit of memoization. Testing such queries over small test databases will typically not alert the developer to the possibility of performance issues in a production setting.
With SQL Anywhere, the performance of user-defined SQL functions is also impacted because stored procedures and functions in SQL Anywhere are interpreted, rather than compiled, though this is not really a black-and-white distinction; there are optimizations in SQL Anywhere’s implementation that avoid the complete re-parsing of stored procedures. The tradeoff with this approach is ease-of-use; with SQL Anywhere procedures can be altered at (almost) any point, and can reference objects, such as tables or temporary tables, that do not exist when the procedure or function is created.
In query processing, switching within a plan operator to the execution of a stored module, even if the module is compiled, as with Oracle, is relatively expensive. Precisely how expensive can be difficult to estimate, because of factors like memoization which will have differing properties with different access plans.
An estimate of how expensive UDF stored functions are in relation to “ordinary” processing of a SELECT statement is provided in this Oracle 12c new feature document, written by Bryn Llewellyn, a Distinguished Product Manager with Oracle’s Database Server Technologies Division. Bryn’s example compares the execution of a SELECT statement projecting four attributes, three of which whose value is a CASE expression returning a string:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27
SELECT PK, CASE WHEN n1 <= 1023 THEN TO_CHAR(n1, '999999')||' byte' WHEN n1/1024 <= 1023 THEN TO_CHAR(n1/1024, '999999')||' K' WHEN n1/1048576 <= 1023 THEN TO_CHAR(n1/1048576, '999999')||' M' WHEN n1/1073741824 <= 1023 THEN TO_CHAR(n1/1073741824, '999999')||' G' ELSE TO_CHAR(n1/1099511627776, '999999')||' T' END "n1", CASE WHEN n2 <= 1023 THEN TO_CHAR(n2, '999999')||' byte' WHEN n2/1024 <= 1023 THEN TO_CHAR(n2/1024, '999999')||' K' WHEN n2/1048576 <= 1023 THEN TO_CHAR(n2/1048576, '999999')||' M' WHEN n2/1073741824 <= 1023 THEN TO_CHAR(n2/1073741824, '999999')||' G' ELSE TO_CHAR(n2/1099511627776, '999999')||' T' END "n2", CASE WHEN n3 <= 1023 THEN TO_CHAR(n3, '999999')||' byte' WHEN n3/1024 <= 1023 THEN TO_CHAR(n3/1024, '999999')||' K' WHEN n3/1048576 <= 1023 THEN TO_CHAR(n3/1048576, '999999')||' M' WHEN n3/1073741824 <= 1023 THEN TO_CHAR(n3/1073741824, '999999')||' G' ELSE TO_CHAR(n3/1099511627776, '999999')||' T' END "n3" FROM t
The contrasting example is semantically equivalent to the query above, but this version uses a SELECT statement that specifies an in-lined a user-defined PL/SQL function to convert the values.
28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49
WITH FUNCTION Print(n IN INTEGER) RETURN VARCHAR2 IS K CONSTANT NUMBER NOT NULL := 1024; M CONSTANT NUMBER NOT NULL := K*K; G CONSTANT NUMBER NOT NULL := M*K; T CONSTANT NUMBER NOT NULL := G*K; BEGIN RETURN CASE WHEN n <= K-1 THEN TO_CHAR(n, '999999')||' byte' WHEN n/K <= K-1 THEN TO_CHAR(n/K, '999999')||' K' WHEN n/M <= K-1 THEN TO_CHAR(n/M, '999999')||' M' WHEN n/G <= K-1 THEN TO_CHAR(n/G, '999999')||' G' ELSE TO_CHAR(n/T, '999999')||' T' END; END Print; // Utilize the user-defined PL/SQL-FUNCTION within the query SELECT PK, Print(n1) "n1", Print(n2) "n2", Print(n3) "n3" FROM t
With Oracle 12c, version 12.1, here is the performance comparison:
|SELECT statement without UDF||5x|
|Schema-level function using pragma UDF||3.9x|
|In-lined UDF using the with clause||3.8x|
|Pre-12.1 ordinary schema-level function||1.0 – base case|
The take-away from these results is that the in-lined execution cost (3.8x, versus an ordinary PL/SQL stored function) is that the cost includes the compilation of the PL/SQL function. That is a considerable achievement, though it is impossible to know to what extent the compilation cost is amortized per-row of the query since the size of table “t” is unknown. Nonetheless, all database application developers should be aware that using UDF functions in SQL statements results in additional query evaluation overhead. There is always going to be an increase in query execution cost, and that cost can vary due to a variety of factors at execution time, including the possible benefits of memoization, placement of the function within the query’s execution plan, and the query’s degree of parallelism.