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
 

SQL Performance Analysis with SAP PlanViz

In our first post, Performance analysis and optimization of SAP SQL, we presented different approaches to performance analysis of SQLScript. While the EXPLAIN PLAN command can provide some initial clues, in my opinion there is a much better tool for performance analysis - PlanViz. It displays the execution plan in a graphical editor.

The graphical interface greatly facilitates analysis, especially for very large queries that involve multiple steps. PlanViz allows you to identify time consuming steps at a glance. Besides, you'll be able to check how much data is being processed and see if the processing is done in parallel. Using the information provided, you can also see whether only the tables that are actually required are being read or whether too many data records have been selected.

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 

 

You can open PlanViz in Eclipse by right-clicking on the code. To do so, select Visualize Plan → Execute from the context menu.

201-visualize-plan-execute_PlanViz

Eclipse switches to the PlanViz perspective. First an overview with the execution time, the most important operators and the data flow is displayed.

202-planviz-overview

To get more details about an operator, you can click on it. Under Number of Tables Used you can see how many tables are read in total. If you click on the number, you can see the tables used and the number of entries read.

Under the Executed Plan menu item you can see a graphical representation of the execution plan. Each node represents an operation and the arrows show the data flow between the operations. Two execution times are displayed for each node: inclusive and exclusive time. The inclusive time corresponds to the execution time of the entire node. The exclusive time, on the other hand, shows only the runtime of the respective operation.

203-executed-plan_PlanViz

You can expand or collapse details of the respective nodes via the triangle. In the collapsed state, the displayed runtime corresponds to the sum of the underlying nodes. In the expanded state, you can see further information. For example, you can see that if you switch between the different engines, the result is materialized. Since this requires additional resources, this step should be avoided if possible.

006-engine-change-result_PlanViz

In addition, you can display the critical path. The operators that lie on the critical path should be primarily considered for performance optimization. Optimizations that are off the critical path have no impact on overall performance and are therefore secondary.

205-highlight-critical-path_PlanViz

However, the representation of the data flow can quickly become confusing, especially with more complex SQL queries. For better analysis, PlanViz also provides information in table format, which can be found at the bottom of the tool: Timeline, Operator List, Tables Used as well as Performance Trace. In the following We will present these views in more detail.


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

Neuer Call-to-Action


Timeline

The Timeline view provides a complete overview of the execution plan, where the individual operations are placed on a timeline. This allows you to see at a glance if and which operations are being processed sequentially instead of in parallel. In addition, you can find other causes of long-running queries by identifying operations that take up the most runtime and comparing them to your expectations.

206-timeline_PlanViz

The view provides the following information. The X-axis displays the elapsed time since the query was executed. The Y-axis represents a list of the executed operations. The duration of the execution is displayed in the form of a Gantt chart. In addition, you can see the type of each timestamp, for example, whether it is an open, close or fetch step.

If you notice gaps or multiple occurrences of open or fetch statements in the row engine, this indicates that another operation was called by the column engine in the meantime. As you have already learned in our article SQLScript Performance Tips, switching between engines has a negative impact on performance, since the results have to be materialized.

Operator List

The Operator List view provides extended insights into all operators of the execution plan. This view offers much more details than the EXPLAIN FOR functionality we presented in the article Performance analysis and optimization of SAP SQL.

007-operator-list

Especially useful is the possibility to filter the presented data according to certain criteria. For example, under the CP (critical path) column you can see whether the operator is on the critical path. Using a critical path filter, you can focus your analysis on the most important operators.

In addition, you can use the filter to view all operators that process a certain number of rows. Alternatively, you can only look at the long running operations by using the filter for execution time.

208-operator-list-filter_PlanViz

This allows you to focus your analysis on the essentials. Double-clicking on an entry takes you to this operator in the graphic display.

Tables Used

This view provides an overview of the tables used during the processing of your SQL query. Three key figures are displayed per table: maximum number of processed entries, number of accesses and maximum processing time. You can sort the content in ascending or descending order to identify outliers faster.

Here you can see at a glance whether the processed tables and the number of entries make sense. For example, if you discover a table that is not needed from the business point of view, you can adjust your SQL logic so that the table is not accessed. In addition, you can take a look at the number of entries processed. If the number of entries turns out to be higher than expected, check whether the filtering in your SQL query can be improved.

209-tables-used_PlanViz

Double-clicking on a row takes you to the Operator List view, filtering on the specific table. This way you can see all operators working with this table.

208-operator-list-filter_PlanViz

Performance Trace

As the name suggests, this view is used for performance analysis. It shows a list of operators with the respective runtime and number of processed lines. This view consists of two areas. On the left, you see a list of operators that you can select. On the right, the performance trace data for the selected operator is displayed.

211-performance-trace

This view is primarily targeted at advanced users who are well acquainted with the details of SAP HANA. You can use this view to display additional information for a particular operator that is not available in other views. By looking at the sequential list of activities, you may be able to fill in the gaps in the Timeline view. In addition, you can view the detail of the engine execution.

Network View

In the Network view, you can view the data transfers between different servers in your network. You can use this view to identify any problems with your distributed environment.

For example, if you notice that a particular data transfer takes an unusually long time, you can check the data size as well as the network. If you find that there are frequent data transfers between a certain group of servers, you can improve the setup.

212-network

PlanViz - Our Summary

We hope that you have now gained a basic understanding of how PlanViz works. It is a very powerful tool that allows you to analyze various performance aspects of your SQL queries.

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