Foundation1 / Development Guide / Core Server / Base Server Dev / Performance / This page

Functions in Select and Where

Since Oracle 7.1 it is possible to include stored function calls within an SQL-statement. This is a nice solution, but you have to pay attention to performance since context switching between SQL and PLSQL is expensive. So please use PL/SQL functions carefully in your SQL statements, especially in WHERE clauses. The included functions might be called more often than you think they might be. Decreasing the use of such function calls will improve the overall performance.

Contents

• Limit the use of PLSQL methods in the SELECT list
• Limit the use of PLSQL methods in the WHERE clause
• Limit the use of PLSQL methods in VIEWS

Limit the use of PLSQL methods in the SELECT list

Example:

• Join tables/views instead of using Get functions in the select list when possible
• Call the function in plsql code when the cursor values have been fetched, to avoid context switching.

Original cursor:

The above can be rewritten by taking off the method call in the SELECT statement. Instead the method call is inside plsql code, therefore no context switch is needed.

Limit the use of PLSQL methods in the WHERE clause

There are disadvantages of using PLSQL methods inside the where clause, they are as follows:

• Function gets executed for each record in the data-source.
• Functions in the WHERE clause may disable the use of index.
• Prevent the optimizer from assigning valid cardinality or selectivity estimates which, can in turn, effect the overall execution plan and the join method.

Example:

A function call in the where clause could be a performance killer.

Solution:

Move the method call from the cursor into PLSQL

Limit the use of PLSQL methods in VIEWS

Often, it is very easy to place stored functions in the private base method views. This will lead to ”large”, also known as ”fat” views. It is better to place the function calls as columns in the client than placing them in views of the logical unit. Since the views might be taken from anywhere, and very often from the client, the rules from above should be considered when designing views. Please note that the client server communication model in Foundation1 is constructed to use ”thin base views”.

• In where clauses of view definitions
  Having functions on column values in the WHERE-clause will dramatically affect the performance, when no indexes may be used and a query can lead to a full table scans in the database. If security aspects are included in the (base) view, try to include it with a join or sub select rather than a PLSQL method.
• In key columns in the base view definition
  Using stored functions in key columns will always lead to performance bottlenecks for logical units containing normal amount of data, even if queries are made on "key columns".
• In base views
  When using many functions in the view definition, this extra work must be made even for the operations when the view is used inside the implementation of the base methods. This will of course affect performance. A superior way is to define the function at the client side as a pseudo-column