What is the Purpose of 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 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 view HANA SQL Analyzer.
Note
To enable this view, when creating your SAP Business Application Studio development space, or later on, you must add the extension SAP HANA Performance Tools to this space.
The SQL Analyzer is a set of views, tables, graphs that you can use to analyze any SQL Query. You can drill-down in a graph execution, analyze the timeline of query compilation and execution, visualize how many tables, and which tables, were used, and so on.
In addition to analyzing the SQL generated by the calculation views (either the standard query or a modified query), it can be used wherever SQL is defined, for example in table functions.
How to Get the Query Statement?
To generate the plan file of an SQL query, you must put it in an SQL Console. You do not need to execute it.
For a calculation view, generating the SQL query can be done in the following ways:
- In the SAP HANA Database Explorer view of SAP Business Application Studio, select the HDI container, right-click the column view and choose Generate SQL Statement.
This is the easiest way, because from within the embedded SAP HANA Database Explorer view, the generated plan file will open directly in the HANA SQL Analyzer view.
- In the Explorer view, navigate to the design-time file of the calculation view, right-click and choose SQL Editor. You can also choose Data Preview and, in the Raw Data tab, choose the Edit SQL Statement in SQL Console icon.
- In the external SAP Database Explorer, select the HDI container, select the column view, right-click and choose Generate SQL Statement.
The two last methods are a bit less straightforward, as they require to download the SQL plan file (.plv) from the SQL Console and upload it to the HANA SQL Analyzer view in Business Application Studio.
Note
In any scenario, it is always possible to modify the default query. For example, removing some columns to make sure node/data source pruning works as expected. It is also possible to create the SQL query on top of a calculation view from scratch.Generating the SQL Analyzer Plan File
To get started, you generate a file that collects the run-time information of your SQL query. Use the menu option Analyze → Generate SQL Analyzer Plan File. Choose a file name prefix for the plan file, and choose Save. The location is already set for you and you cannot change it at this stage.
If you have generated the SQL plan file from an SQL Console opened from the SAP HANA Database Explorer view of SAP Business Application Studio, the plan file opens immediately in the HANA SQL Analyzer view.
You can then proceed to the last step of this procedure.
If you have generated the SQL plan file from another tool, such as the Data Preview of SAP Business Application Studio, or from the external SAP HANA Database Explorer (outside of SAP Business Application Studio), you need to download the SQL plan file to your computer. Choose Download, and this is then handled by your web browser. Choose a convenient location to store the SQL plan file.
The next stage is to upload the SQL analyzer plan file to the Explorer view of SAP Business Application Studio. Upload the plan file to any project folder by using the context menu Upload.
Switch to the SQL Analyzer.
Note
In order to see the HANA SQL Analyzer view, remember to add the extension SAP HANA Performance Tools to your development space. This can only be done when the development space is stopped.
Add the plan file to HANA SQL Analyzer.
You must first locate the plan file in the folder where you uploaded it.
- Select the plan file to display the results in the main window.
How to Analyze a Generated SQL Plan File Later on?
In case you want to generate plan files first, and access the generated SQL plan files at a later time, you can proceed as follows, depending on how you generated the plan files:
- Embedded SAP HANA Database Explorer (in SAP Business Application Studio):
You access these plan files from the Explorer view, in the folder
/home/user/.vscode/data/User/globalStorage/sapse.hana-database-explorer/. They can be opened directly from this location or, If relevant, you can move the plan files to your project. - External SAP HANA Database Explorer and other views of SAP Business Application Studio:
You access these plan files from the External SAP HANA DB Explorer, within the HDI container, in the section Catalog → Database Diagnostic Files → <DB Instance ID> → other. They must be downloaded, and then uploaded to SAP Business Application Studio.