How to improve the performance of your SQL functions

Sebastian

Written By: Sebastian - 04 May 2023

While other tools check the performance of the execution, i.e. the result of the written code, SQLScript Code Analyzer is used to check the code itself. This tool can be used for existing procedures or functions, as well as before creating them. It checks the quality, security and performance of the code. The results of the check are output in the form of a table.

In this article we will first explain the basics and then show how this tool works using concrete examples. You can use the code snippets provided in the process for your own implementations.

Our blog series "SQLScript Performance Analysis" at a glance

  1. Performance analysis and optimization of SAP SQL 
  2. SQL performance analysis with SAP PlanViz 
  3. How to improve the performance of your SQL functions 

 

Two procedures and a rules table

For the analysis of the code SAP provides two procedures ANALYZE_SQLSCRIPT_OBJECTS and ANALYZE_SQLSCRIPT_DEFINITION. Below we present each procedure in detail. The rules are contained in the sqlscript_analyzer_rules table and can be viewed using the following statement:

 SELECT * FROM sqlscript_analyzer_rules; 

This table is available in both on-premises HANA systems and in the HANA Cloud. The rules in the table can be extended over time as new service packages are released. At the moment, the following rules are available:

301-sql-script-analyzer-rules_SQL functions

ANALYZE_SQLSCRIPT_OBJECTS

Using the ANALYZE_SQLSCRIPT_OBJECTS procedure you can analyze the source code of already existing procedures or functions. You can also check several procedures at once. The objects to be checked are passed as parameters. Altogether the procedure has four parameters, which we explain in the following:

Parameter

Description

OBJECTS

A list of SQLScript procedures and functions to be checked.

RULES

The rules used during the check. Can be taken from the sqlscript_analyzer_rules table.

OBJECT_DEFINITIONS

Contains the names and definitions of all checked objects.

FINDINGS

Contains potential issues identified during testing.

 

Enclosed is an example of calling the procedure ANALYZE_SQLSCRIPT_OBJECTS. All procedures of the SQL Script type in the DRS schema are scanned. It is also possible to scan several schemas at the same time.

DO BEGIN
lt_rules =
  SELECT rule_namespace,
                   rule_name,
                   category
  FROM sqlscript_analyzer_rules;

lt_procedures =
  SELECT  schema_name,
                    procedure_name AS object_name, --object_name erwartet
                    definition
  FROM sys.procedures  
    WHERE procedure_type = 'SQLSCRIPT2' AND schema_name
IN('DRS','[SCHEMA_NAME]','[ANOTHER_SCHEMA_NAME]');

CALL analyze_sqlscript_objects( :lt_procedures,
                                :lt_rules,
                                 lt_objects,
                               lt_findings);

  SELECT objects.schema_name, objects.object_name, findings.*,
objects.object_definition
FROM :lt_findings AS findings  
JOIN :lt_objects AS objects  
ON objects.object_definition_id = findings.object_definition_id;
END;

The result of the analysis can be seen in columns RULE_NAME and SHORT_DESCRIPTION. The OBJECT_DEFINITION column shows the affected code.

302-ergebnis-der-analyse_SQL functions

The whole thing also works with functions. You just have to replace procedure with function in the call:

DO BEGIN
lt_rules =
  SELECT rule_namespace,
                   rule_name,
                   category
    FROM sqlscript_analyzer_rules;

lt_functions =
  SELECT  schema_name,
                    function_name AS object_name, --object_name erwartet
                    definition
  FROM sys.functions  
    WHERE function_type = 'SQLSCRIPT2' AND schema_name
IN('DRS','[SCHEMA_NAME]','[ANOTHER_SCHEMA_NAME]');

CALL analyze_sqlscript_objects( :lt_functions,
                                :lt_rules,
                                lt_objects,
                                 lt_findings);

  SELECT objects.schema_name, objects.object_name, findings.*,
objects.object_definition
FROM :lt_findings AS findings  
JOIN :lt_objects AS objects  
ON objects.object_definition_id = findings.object_definition_id;
END;

Increase the performance of your BW with SQLScript!
Click here for the whitepaper!

Neuer Call-to-Action


ANALYZE_SQLSCRIPT_DEFINITION

Unlike the previously presented procedure, which scans the code repository for potential improvements, ANALYZE_SQLSCRIPT_DEFINITION can be used to analyze the source code of procedures or functions that have not yet been created. It is useful to check the code before creating a procedure. The procedure is similar to ANALYZE_SQLSCRIPT_OBJECTS - you pass the rules and the source code as parameters. The results are output in the form of a table. The procedure ANALYZE_SQLSCRIPT_DEFINITION has the following parameters:

Parameter

Description

OBJECT_DEFINITIONS

Contains the source code to be checked.

RULES

The rules used during the check. Can be taken from the sqlscript_analyzer_rules table.

FINDINGS

Contains potential issues identified during testing.

 

The procedure can be called like this, for example:

DO BEGIN
lt_rules =
SELECT rule_namespace,
rule_name,
category
FROM sqlscript_analyzer_rules;

CALL analyze_sqlscript_definition(
'
CREATE PROCEDURE concatenate_name(
IN lv_firstname NVARCHAR(30),
IN lv_lastname NVARCHAR(30),
OUT lv_name NVARCHAR(62)
)
AS BEGIN
DECLARE lv_output NVARCHAR(62) default '' '';
lv_name = lv_firstname || '' '' || lv_lastname;
END;
',
:lt_rules,
lt_findings);
SELECT * FROM :lt_findings;
END;

The results are displayed in a table just like with ANALYZE_SQLSCRIPT_OBJECTS.

303-ergebnis-der-quellcode-analyse_SQL functions

The whole thing also works analogously with the CREATE FUNCTION statement:

DO BEGIN
lt_rules =
SELECT rule_namespace,
rule_name,
category
FROM sqlscript_analyzer_rules;

CALL analyze_sqlscript_definition(
'
CREATE FUNCTION udf_concatenate_name(
lv_firstname NVARCHAR(30),
lv_lastname NVARCHAR(30)
)
RETURNS lv_name NVARCHAR(62)
AS BEGIN
lv_name = lv_firstname || '' '' || lv_lastname;
END;
',
:lt_rules,
lt_findings);
SELECT * FROM :lt_findings;
END;

SQL functions - Our Summary

In this article, you learned about two helpful procedures to increase SQLScript performance. On the one hand, you can scan and improve existing procedures and functions. On the other hand, you can check your source code already in the specification phase and thus prevent possible performance problems from the beginning.

Do you have questions about SAP HANA SQLScript? Are you trying to build up the necessary know-how in your department or do you need support with a specific question? We are happy to help you. Request a non-binding consulting offer today!

Learn more about  SAP HANA SQLScript

Topics: SAP HANA SQL

Share article