How to investigate if Table User-Defined Function is unfolded or not?

[former_member390378]

Sometimes the performance bottleneck of query execution can be a Table User-Defined Function (TUDF). To improve query performance, you need to further analyze the TUDF execution plan.

Introduction

To check the TUDF execution plan you can use the existing EXPLAIN PLAN functionality.

Up until SAP HANA 2 SPS 03, the EXPLAIN PLAN for SQLScript TUDF did not provide any details on its operations. Starting SAP HANA 2 SPS 04, the EXPLAIN PLAN offers more insights like if TUDF is unfolded or not, what is the unfolding blocker, a comma-separated list of objects that are used within the TUDF, etc.

TUDF Unfolding is an important topic because it enables SQL optimizer to apply its optimizations to generate a good execution plan.

Illustration

Let's take an example and work our way through it.

First I create a schema EMPLOYEE, a table EMP_TABLE, and insert 3 records in EMP_TABLE.

CREATE SCHEMA EMPLOYEE;

set schema EMPLOYEE;

CREATE TABLE EMP_TABLE (empid INT);



INSERT into EMP_TABLE (empid) values (100);

INSERT into EMP_TABLE (empid) values (200);

INSERT into EMP_TABLE (empid) values (300);

Next, I create a Table User-defined Function i.e. EMP_FUNC().

CREATE FUNCTION EMP_FUNC() RETURNS TABLE(empid INT)

AS BEGIN

RETURN SELECT * FROM EMP_TABLE WITH HINT(IGNORE_PLAN_CACHE);

END;

Imagine we would like to fetch the employees with empid > 100.

So I write the query to use the TUDF:  SELECT * FROM EMP_FUNC() WHERE empid > 100;

At the same time, I also want to check if the TUDF is unfolded or not as this impacts overall query performance. To check this, I will collect the explain plan for the query. Once the explain plan is generated it can be fetched from explain_plan_table using the statement_name (in this case 'EMP_FUNC_PLAN').

explain plan set statement_name = 'EMP_FUNC_PLAN' for SELECT * FROM EMP_FUNC() WHERE empid > 100;



select statement_name, operator_name, operator_details, operator_properties, schema_name, table_name from explain_plan_table where statement_name ='EMP_FUNC_PLAN';

In the explain plan below, the 2 fields that you want to focus on are OPERATOR_NAME and OPERATOR_PROPERTIES.

In the OPERATOR_NAME column, we see the entry ‘TABLE FUNCTION’. This tells us that the TUDF has not been unfolded.

To understand what blocked unfolding, we need to check the column OPERATOR_PROPERTIES.

OPERATOR_PROPERTIES has the entry ‘NOT UNFOLDED DUE TO WITH HINT’. This gives us the insight that the unfolding is blocked because the TUDF has a hint (i.e. WITH HINT(IGNORE_PLAN_CACHE)) in it.

Let’s create a second TUDF EMP_FUNC_no_hint() without the WITH HINT() and verify if it's unfolded or not:

CREATE FUNCTION EMP_FUNC_no_hint() RETURNS TABLE(empid INT)

AS BEGIN

RETURN SELECT * FROM EMP_TABLE;

END;

Let's collect the explain plan and investigate the details:

explain plan set statement_name = 'EMP_FUNC_No_hint_PLAN' for SELECT * FROM EMP_FUNC_no_hint() WHERE empid > 100;



select statement_name, operator_name, operator_details, operator_properties, schema_name, table_name from explain_plan_table where statement_name ='EMP_FUNC_No_hint_PLAN';

In the explain plan above, the OPERATOR_NAME column has the entry ‘COLUMN TABLE’ not ‘TABLE FUNCTION’. This tells us that the TUDF has been unfolded successfully this time.

Also, the OPERATOR_PROPERTIES column is empty, since there are no unfolding blockers.

If you want to more about the other unfolding blockers, you can find them in the section EXPLAIN PLAN for Table User-Defined Functions of the SAP HANA SQLScript Reference for SAP HANA Platform. I am also listing them below but please refer to the above link for the latest details.

Reasons	Details
NOT UNFOLDED BECAUSE FUNCTION BODY CANNOT BE SIMPLIFIED TO A SINGLE STATEMENT	Multiple statements in TUDF body cannot be simplified into a single statement.
DUE TO ANY TABLE	TUDF uses ANY TABLE type.
DUE TO BINARY TYPE PARAMETER	TUDF has a binary type as its parameter.
DUE TO DEV_NO_SQLSCRIPT_SCENARIO HINT	The caller of TUDF disables unfolding with the DEV_NO_PREPARE_SQLSCRIPT_SCENARIO hint.
DUE TO DEBUGGING SESSION	TUDF is executed in debugging session.
DUE TO ENCRYPTED PROCEDURE OR FUNCTION	TUDF is an encrypted function.
NOT UNFOLDED DUE TO IMPERATIVE LOGICS	TUDF has an imperative logic, including SQLScript IF, FOR,WHILE, or LOOP statements.
DUE TO INTERNAL SQLSCRIPT OPERATOR	TUDF unfolding is blocked by an internal SQLScript operator.
DUE TO INPUT PARAMETER TYPE MISMATCH	The type of the input argument does not match the defined type of the TUDF input parameter.
DUE TO JSON OR SYSTEM FUNCTION	TUDF uses JSON or system function.
DUE TO NATIVE SQLSCRIPT OPERATOR	TUDF has a SQLScript native operator, which does not have an appropriate SQL counterpart.
DUE TO NO CALCULATION VIEW UNFOLDING	The caller of TUDF disables Calculation View unfolding.
DUE TO PRIMARY KEY CHECK	TUDF has a primary key check.
DUE TO RANGE RESTRICTION	Table with RANGE RESTRICTION is used within the TUDF.
DUE TO RECURSION	The TUDF has a recursive call.
DUE TO SEQUENCE OBJECT	A SEQUENCE variable is used within the TUDF.
DUE TO SEQUENTIAL EXECUTION	TUDF is executed with SEQUENTIAL EXECUTION clause.
DUE TO SPATIAL TYPE PARAMETER	TUDF has a spatial type as its parameter.
DUE TO TIME TRAVEL OPTION	TUDF uses a history table OR the time travel option is used.
DUE TO WITH CLAUSE	TUDF uses a WITH clause.
DUE TO WITH HINT	TUDF uses a WITH HINT clause that cannot be unfolded.
DUE TO WITH PARAMETERS CLAUSE	TUDF uses a WITH PARAMETERS clause.
DUE TO XML CLAUSE	TUDF has an XML clause.

Conclusion

Now we know how to check the TUDF execution plan and also how to examine if there is an unfolding blocker as unfolding is crucial to the SQL optimizer to generate an optimal execution plan, once the unfolding blocker is known, you should try to overcome the blocker to improve performance.

Questions/Suggestions? Post a comment.

Thank you.