Analyzing executions with the SQL Analyzer

After completing this lesson, you will be able to:

After completing this lesson, you will be able to:

  • Analyze executions with the SQL Analyzer

Introducing the SQL Analyzer

Although your calculation view may appear well designed, ultimately it is the performance of the calculation view that really counts. You have seen how to display the generated SQL in a calculation view using the Debug Query mode. While this is helpful to see how filters are pushed down and data is pruned, this does not tell us is how the SQL actually performs. We really need to know the answers to the following questions:

  • What was the total execution time?

  • How long did each step take?

  • Are there any steps that are taking significantly longer than other steps?

  • Are there any bottlenecks?

  • How many records did each step process?

  • In what sequence are the steps carried out?

  • Which operators are being called?

  • To what extent was SAP HANA Cloud able to parallelize the query?

  • Which processing engines are being invoked?

  • Were all the query plan steps unfolded?

To help us learn more about the performance of the SQL, we can use the SAP Business Application Studio tool SQL Analyzer.

In addition to analyzing the SQL generated by the calculation views, the SQL Analyzer can be used wherever SQL is defined, for example, in table functions.

The SQL Analyzer is launched from SAP Business Application Studio.

  1. To get started, you first generate a file that collects the run-time information that you will later analyze. To do this, launch the Database Explorer and then, either manually write, or generate the SQL select statement for the calculation view you want to analyze. Once you have the SQL select statement prepared, use the menu option Generate SQL Analyzer Plan File. You will choose a name for the plan file. The location is already set for you and you cannot change this. Other.

  2. Next, locate the generated plan file by opening a catalog database connection and expand the folder Database Diagnostic Files until you reach the folder Other. Here you should find your plan file. Select the file and choose Download.

  3. In SAP Business Application Studio, upload the plan file to any project folder by using the folder option Upload Files.

  4. Switch to the SQL Analyzer. If you don't see this tool, you need to add it as an extension to your development space. To do this, close SAP Business Application Studio and stop your development space. Edit the development space and add the extension SAP HANA Performance Tools. Start the development space and the tool should now appear.

  5. Add the plan file.

  6. Select the plan file to display the results in the main window.

Log in to track your progress & complete quizzes