How to improve the performance of your SQL functions

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.

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:

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:

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.

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!

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:

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.

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!