Skip to content
NextLytics
Megamenü_2023_Über-uns

Shaping Business Intelligence

Whether clever add-on products for SAP BI, development of meaningful dashboards or implementation of AI-based applications - we shape the future of Business Intelligence together with you. 

Megamenü_2023_Über-uns_1

About us

As a partner with deep process know-how, knowledge of the latest SAP technologies as well as high social competence and many years of project experience, we shape the future of Business Intelligence in your company too.

Megamenü_2023_Methodik

Our Methodology

The mixture of classic waterfall model and agile methodology guarantees our projects a high level of efficiency and satisfaction on both sides. Learn more about our project approach.

Products
Megamenü_2023_NextTables

NextTables

Edit data in SAP BW out of the box: NextTables makes editing tables easier, faster and more intuitive, whether you use SAP BW on HANA, SAP S/4HANA or SAP BW 4/HANA.

Megamenü_2023_Connector

NextLytics Connectors

The increasing automation of processes requires the connectivity of IT systems. NextLytics Connectors allow you to connect your SAP ecosystem with various open-source technologies.

IT-Services
Megamenü_2023_Data-Science

Data Science & Engineering

Ready for the future? As a strong partner, we will support you in the design, implementation and optimization of your AI application.

Megamenü_2023_Planning

SAP Planning

We design new planning applications using SAP BPC Embedded, IP or SAC Planning which create added value for your company.

Megamenü_2023_Dashboarding

Dashboarding

We help you with our expertise to create meaningful dashboards based on Tableau, Power BI, SAP Analytics Cloud or SAP Lumira. 

Megamenü_2023_Data-Warehouse-1

SAP Data Warehouse

Are you planning a migration to SAP HANA? We show you the challenges and which advantages a migration provides.

Business Analytics
Megamenü_2023_Procurement

Procurement Analytics

Transparent and valid figures are important, especially in companies with a decentralized structure. SAP Procurement Analytics allows you to evaluate SAP ERP data in SAP BI.

Megamenü_2023_Reporting

SAP HR Reporting & Analytics

With our standard model for reporting from SAP HCM with SAP BW, you accelerate business activities and make data from various systems available centrally and validly.

Megamenü_2023_Dataquality

Data Quality Management

In times of Big Data and IoT, maintaining high data quality is of the utmost importance. With our Data Quality Management (DQM) solution, you always keep the overview.

Career
Megamenü_2023_Karriere-2b

Working at NextLytics

If you would like to work with pleasure and don't want to miss out on your professional and personal development, we are the right choice for you!

Megamenü_2023_Karriere-1

Senior

Time for a change? Take your next professional step and work with us to shape innovation and growth in an exciting business environment!

Megamenü_2023_Karriere-5

Junior

Enough of grey theory - time to get to know the colourful reality! Start your working life with us and enjoy your work with interesting projects.

Megamenü_2023_Karriere-4-1

Students

You don't just want to study theory, but also want to experience it in practice? Check out theory and practice with us and experience where the differences are made.

Megamenü_2023_Karriere-3

Jobs

You can find all open vacancies here. Look around and submit your application - we look forward to it! If there is no matching position, please send us your unsolicited application.

Blog
NextLytics Newsletter Teaser
Sign up now for our monthly newsletter!
Sign up for newsletter
 

Performance analysis and optimization of SAP SQL

There are several ways to analyze SQLScript performance. In this article you will learn about the relevant tools. We will show you how to measure the runtime of your SQL queries, which can serve as a first indication of performance. Then we will explain how to use the execution plan to find out which part of the code is taking the longest.

With the PlanViz tool, you can also view the execution plan graphically and analyze individual steps. This option is available with Eclipse or HANA Studio as well as in web-based frontends such as SAP Business Application Studio. Finally, we will show you how to use the SQLScript Code Analyzer to scan your procedures and functions to generate suggestions for improvement.

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 

 

Runtime measurement

The runtime of a SQL query can be used as a first indication of the script's performance. Especially if you compare two similar queries, the query with the lower runtime is the better performing one.

Note that the runtime of an SQLScript also depends on the general server load and can therefore fluctuate. In order to obtain representative results, you should therefore execute the SQL script several times and then compare the measurement results.

Let's consider the following example, which we presented in our post "How to use Window Functions in SQLScript". First, we run the query with the WHERE clause.

SELECT

    cost."ZDRCOSTLINEITEMID",

    ccgrp."ZDRCOSTCENTREGROUPTEXT",

    ccitem."ZDRCOSTCENTREITEMTEXT",

    cost."CURRENCY",

    cost."COST",

    cost."COSTBOOKINGDATE",

-- regular aggregations

    MAX("COST") OVER (PARTITION BY ccgrp."ZDRCOSTCENTREGROUPTEXT") as "MAX",

    ROUND(AVG("COST") OVER (PARTITION BY ccgrp."ZDRCOSTCENTREGROUPTEXT"), 0) as "AVG",

    COUNT(*) OVER (PARTITION BY ccgrp."ZDRCOSTCENTREGROUPTEXT") as "COUNT",

-- special functions

    LEAD(cost."COSTBOOKINGDATE", 1) OVER (PARTITION BY ccgrp."ZDRCOSTCENTREGROUPTEXT" ORDER BY ccitem."ZDRCOSTCENTREITEMTEXT", cost."COSTBOOKINGDATE") as "LEAD",

    DENSE_RANK() OVER (PARTITION BY ccgrp."ZDRCOSTCENTREGROUPTEXT" ORDER BY cost."COST" DESC) as "D_RANK",

    ROW_NUMBER() OVER (PARTITION BY ccitem."ZDRCOSTCENTREITEMTEXT" ORDER BY cost."COSTBOOKINGDATE") as "ROWNUM"    

FROM

    ZDRCOSTCENTREGROUP as ccgrp

    left join

    ZDRCOSTCENTREITEM as ccitem on ccgrp."ZDRCOSTCENTREGROUPID" = ccitem."ZDRCOSTCENTREGROUPID"

    left join

    ZDRCOSTLINEITEM as cost on ccitem."ZDRCOSTCENTREITEMID" = cost."ZDRCOSTCENTREITEMID"

WHERE

    ccgrp."ZDRCOSTCENTREGROUPTEXT" = 'Administration'

ORDER BY

    ccitem."ZDRCOSTCENTREITEMTEXT", cost."COSTBOOKINGDATE";

The runtime and the number of selected rows are displayed in the SQL console.

001-laufzeitmessung-mit-where_SAP SQL

Now we execute the same query without the WHERE clause. So all data will be selected. From the SQL console we can see that the runtime is almost double. Whereby the server processing time is increased tenfold. Also ten times as many rows are selected.

002-laufzeitmessung-ohne-where-klausel_SAP SQL

Generate execution plan

The EXPLAIN PLAN command allows you to generate an execution plan of your SQL query in tabular form. This will display a list of operators used by the database to execute the query, as well as supplementary information about them. This way you can find out which operators are responsible for the long runtime. This information can be used to optimize the query, for example, by reducing the runtime or memory consumption.

003-explain-plan-for_SAP SQL

The execution plan can be generated using the following syntax:

EXPLAIN PLAN [SET STATEMENT_NAME = *<statement_name>*] FOR SELECT *<subquery>*

If you specify the statement, the result is stored in the explain_plan_table table.

EXPLAIN PLAN SET STATEMENT_NAME = 'MyStatement01' FOR

SELECT

    cost."ZDRCOSTLINEITEMID",

    ccgrp."ZDRCOSTCENTREGROUPTEXT",...

This table can be read out subsequently:

SELECT * FROM

explain_plan_table

WHERE

           statement_name = 'MyStatement01'

Alternatively, the execution plan can be created directly at runtime by omitting the SET STATEMENT_NAME parameter. The execution plan is displayed in the Result window. You can also enter multiple queries separated by a semicolon to generate multiple execution plans simultaneously.

EXPLAIN PLAN FOR

SELECT

    cost."ZDRCOSTLINEITEMID",

    ccgrp."ZDRCOSTCENTREGROUPTEXT",

    ccitem."ZDRCOSTCENTREITEMTEXT",

    cost."CURRENCY",

    cost."COST",

    cost."COSTBOOKINGDATE",

-- regular aggregations

    MAX("COST") OVER (PARTITION BY ccgrp."ZDRCOSTCENTREGROUPTEXT") as "MAX",

    ROUND(AVG("COST") OVER (PARTITION BY ccgrp."ZDRCOSTCENTREGROUPTEXT"), 0) as "AVG",

    COUNT(*) OVER (PARTITION BY ccgrp."ZDRCOSTCENTREGROUPTEXT") as "COUNT",

-- special functions

    LEAD(cost."COSTBOOKINGDATE", 1) OVER (PARTITION BY ccgrp."ZDRCOSTCENTREGROUPTEXT" ORDER BY ccitem."ZDRCOSTCENTREITEMTEXT", cost."COSTBOOKINGDATE") as "LEAD",

    DENSE_RANK() OVER (PARTITION BY ccgrp."ZDRCOSTCENTREGROUPTEXT" ORDER BY cost."COST" DESC) as "D_RANK",

    ROW_NUMBER() OVER (PARTITION BY ccitem."ZDRCOSTCENTREITEMTEXT" ORDER BY cost."COSTBOOKINGDATE") as "ROWNUM"    

FROM

    ZDRCOSTCENTREGROUP as ccgrp

    left join

    ZDRCOSTCENTREITEM as ccitem on ccgrp."ZDRCOSTCENTREGROUPID" = ccitem."ZDRCOSTCENTREGROUPID"

    left join

    ZDRCOSTLINEITEM as cost on ccitem."ZDRCOSTCENTREITEMID" = cost."ZDRCOSTCENTREITEMID"

WHERE

    ccgrp."ZDRCOSTCENTREGROUPTEXT" = 'Administration'

ORDER BY

    ccitem."ZDRCOSTCENTREITEMTEXT", cost."COSTBOOKINGDATE";

        

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

Neuer Call-to-Action


Alternatively, you can right-click on the code in Eclipse and select Explain Plan from the menu.

004-explain-plan-rechtsklick_SAP SQL

In the SAP HANA Database Explorer, you can find this option under the Analyze drop-down menu.

005-explain-plan-hana-database-explorer_SAP SQL

PlanViz

Explain Plan provides initial approaches to performance analysis. However, in my opinion, there is a much better tool that makes Explain Plan obsolete - PlanViz. Unlike Explain Plan, PlanViz presents the execution steps graphically, which makes analysis much easier. After the initial analysis of the aggregated steps, you can dive deeper into the details and look at the individual operators.

006-engine-change-result_SAP SQL

At the same time, the tabular view is not lost. PlanViz offers five tabular views in addition to the graphical view. In these you can view details about timeline, operators, tables and performance as well as network.

007-operator-list_SAP SQL

Since this is a really powerful and extensive tool, we have summarized further information in a separate article - SQL Performance Analysis withSAP PlanViz.

SQLScript Code Analyzer

While PlanViz is useful for post factum analysis, SQLScript Code Analyzer is used to examine your SQL functions and procedures for potential code quality, security and performance issues. By ensuring clean code, you can prevent performance problems in the first place.

For this purpose, SAP delivers two procedures that can analyze the source code of your functions and procedures: ANALYZE_SQLSCRIPT_OBJECTS and

ANALYZE_SQLSCRIPT_DEFINITION. The ANALYZE_SQLSCRIPT_OBJECTS procedure is used to analyze existing objects. Alternatively, ANALYZE_SQLSCRIPT_DEFINITION can be used to examine the source code for potential improvements before the function or procedure is created.

Web Tools

The tools described above primarily refer to the Eclipse or HANA Studio environment. However, in addition to these, web-based tools can also be used for performance analysis. For example, the Explain Plan functionality is also available in the HANA Database Explorer.

008-generate-sql-analyzer-plan-file_SAP SQL

You can also use the Generate SQL Analyzer Plan File menu item to generate the plv files. These are stored under "Database Diagnostic Files" and can be opened with Eclipse in the PlanViz perspective. Alternatively, you can also use the integrated SQL Analyzer for this.

In addition, you can generate the plv files via the Generate SQL Analyzer Plan File menu item. These are stored under "Database Diagnostic Files" and can be opened with Eclipse in the PlanViz perspective. Alternatively, you can also use the integrated SQL Analyzer for this purpose.

This tool is available both in the Web IDE and in the SAP Business Application Studio. SQL Analyzer is similar to the previously presented PlanViz. In addition to the graphical view, the tabular views provide more details about the operators, the sequence, the tables used and the number of table accesses.

009-hana-sql-analyzer_SAP SQL

Performance analysis and optimization - Our Summary

You now have an overview of the various options for SQLScript Performance Analysis. In the next article, SQL Performance Analysis withSAP PlanViz, we will introduce PlanViz in detail.

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

avatar

Sebastian

Sebastian Uhlig has been consulting companies in various industries on SAP BI solutions at national and international level since 2001 and covers the range from requirements analysis to the implementation of complex solutions. He has experience in leading project teams and is the architect of the product NextTables. He enjoys mountain biking and watching American football games.

Got a question about this blog?
Ask Sebastian

Blog - NextLytics AG 

Welcome to our blog. In this section we regularly report on news and background information on topics such as SAP Business Intelligence (BI), SAP Dashboarding with Lumira Designer or SAP Analytics Cloud, Machine Learning with SAP BW, Data Science and Planning with SAP Business Planning and Consolidation (BPC), SAP Integrated Planning (IP) and SAC Planning and much more.

Subscribe to our newsletter

Related Posts

Recent Posts