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
Sign up now for our monthly newsletter!
Sign up for newsletter
 

SQLScript Performance Tips

Performance is one of the main arguments for using SQLScript. With the introduction of the HANA database, SAP has introduced the "code pushdown" concept. With this new paradigm, time-consuming calculations with high data volumes are delegated to the database level. Using SQLScript, you have native access to the HANA database and can avoid unnecessary data transfers between the database and the application server.

2019-08-22 13_50_12-berechnungen_SQLScript

However, you can also design your SQL code in such a way that the programs run very slowly. Therefore, in this article we present proven tips to achieve high SQLScript performance.

Reduce data volume

High performance is more achievable when working with a small amount of data. Therefore, limit the data volume as early as possible and select only the data that you really need. A few easy-to-follow rules will help you:

Limit columns in the SELECT statement

Get into the habit of always explicitly selecting the columns of a table. So instead of just selecting everything, as in the following statement ...

SELECT * FROM Customers;

... you should include the required columns in the statement:

SELECT

    CustomerName,

    City

FROM

    Customers;

 

Precise WHERE clauses

In addition to explicitly selecting columns, you should always include the WHERE clause in your SELECT statements. This is used to filter the data. This way, only the records that meet a certain condition are extracted. Because often only a subset of all existing data is needed for the calculations.

SELECT

    CustomerName,

    City

FROM

    Customers

WHERE

    Country='Germany';

Please note that the HAVING clause does not have the same effect. While the WHERE clause reduces the amount of data during selection, the HAVING clause forms a filter on the already grouped and aggregated data. Therefore, the HAVING clause cannot be used to improve performance.

Define client

If you have worked with ABAP before, you do not care about the client any further, because it has been selected implicitly in OpenSQL. With SQLScript, on the other hand, you must always explicitly define the required client.

By filtering out the data of other clients, you increase performance. This is especially true for joins. If the client is not defined, the Cartesian product of all clients is used for the result.


Increase the performance of your BW with SQLScript

Neuer Call-to-Action


Avoid switching between Row and Column Engine

When processing SQLScript queries, SAP HANA uses different engines. Basically, a distinction can be made between the Row Engine and the Column Engine. These are specialized for different tasks. To achieve the highest possible performance, you should avoid switching between the engines in your code.

Thus, the row-by-row processing, for example in the case of a window function, takes place in the Row Engine. In contrast, joins, aggregations and calculations are performed in the Column Engine. If it is necessary to switch between the engines when executing a query, the data must be materialized. This comes at the expense of performance.

Use declarative statements

Just like switching between Row and Column Engine, you should avoid switching between declarative and imperative statements. The reason is that only declarative statements can be optimized by SAP HANA. Therefore, you should use them preferentially and avoid imperative statements.

What is the difference between declarative and imperative statements? With declarative statements, you tell the system what data you would like to have. The system independently finds the best way to provide you with the data. With imperative statements, on the other hand, you explicitly tell the system how it should deliver the data. However, this "micromanagement" also limits the system's potential to optimize execution.

Declarative statements include SELECT, JOIN, WHERE as well as clauses such as WITH, GROUP BY, HAVING, ORDER BY and also predefined functions such as MAP. Imperative statements include FOR and WHILE LOOP as well as IF ELSE branches.

No DML statements in loops

While loops are generally bad for performance, it can be made worse by executing Data Manipulation Language (DML) statements inside the loops. This includes any statements that read or modify data in tables. For example SELECT, INSERT, UPDATE or DELETE.

Instead of inserting or deleting data within the loops, try to bundle the adjustments together and then execute them in a single statement whenever possible.

Avoid scalar functions

In contrast to the table function, the scalar function returns a scalar (i.e. a single) value. In general, scalar functions perform worse in terms of performance. The poor performance can be especially apparent with large amounts of data. Before going live, you should therefore run a performance test with a sufficiently large amount of data.

In addition, you should not execute SELECT queries in scalar functions for performance reasons. These are executed very often, which is detrimental to performance. Please note that this also includes queries to the DUMMY table.

If your scalar function always returns the same result given constant input parameters, you can use the DETERMINISTIC keyword to turn on buffering. It is set after the definition of the parameters. Buffering is particularly useful for functions with binary results. If you expect different results, DETERMINISTIC should not be used, because the performance may even be worse due to caching.

Our Summary - SQLScript Performance

Now you know the typical pitfalls and how to avoid them. Follow our guidelines and get the most out of your SQLScript programs!

Do you have questions about SQLScript? Or do you want to convert your transformation routines to SQLScript and are looking for experienced developers with SQLScript know-how? Please do not hesitate to contact us.

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