Thomas Hammer last week wrote a blog introducing new features available with Q4 2022 release of SAP HANA Cloud. I would like to also mention some enhancements done for SQL query execution for 2022 overall.

If you have been using the recent SAP HANA Cloud versions, you may have noticed improvements in execution time while using less memory for the same SQL statements. The engineering team has worked hard to deliver such improvements for the last couple of years and recently we are gradually switching over to the newer execution engine as the coverage of functionality is nearly completed to replace the existing engines such as Join, OLAP and ESX. As you may already know, the enhanced engine is HEX which was initially introduced with the on-premise version SAP HANA 2.0 SPS02.

The benefit of the new execution engine is

• All functionalities are combined to be executed in a single engine – Previously, switching between engines was unavoidable which required materialization when moving data from one engine to another. This required a lot of memory for the materialization. As all functionality is being combined, there is no longer a need for such materialization. Additional benefit is improved performance as such materialization is avoided.
• Just-In-Time code compilation – This prepares an execution plan compiled just prior to the execution exactly suited for the query itself. Previous engines generates pre-defined operators that are plugged together during execution. The downside of JIT compiled plans are increased preparation times as this requires expensive binary compilation of the JIT code. We are addressing this with a plan independent cache for compiled binaries and a slower, but quicker to start interpretation of generated code and different strategies to decide on when to trigger actual binary compilation.
• Tight integration between HEX and SQL Optimizer –  Existing engines had its own internal optimizers in addition to the SQL optimizer and actual execution path may have been different even with the same logical plan in the SQL plan cache. With HEX, it is now more tightly integrated. To minimize the overall preparation time, SQL Plan Stability can be applied. Once the Abstract SQL Plan is cached and applied, the logical enumeration of the prepartion step can be bypassed improving the preparation time and JIT  code compilation is planned to be improved. We are planning to activate ASP capturing by default starting from Q1 2023 and will announce new features that will utilize this in the coming quarters.
• Executions are pipelined and parallelized – The executions are pipelined and each pipeline is also parallelized. For example, joining 2 tables as hashjoin, the logical plan is as on the left side of the diagram below. For the execution plan, each table is processed as different pipelines and each pipeline is parallelized for faster execution.

Data is processed in chunks – The data is read and processed in chunks through the pipeline consuming less memory as the entire data doesn’t needed to be read first which executing each operators. The SQL execution engine is now able to return data using lesser memory compared to existing engines.

Based on our internal testing, we have identified big improvements when queries are executed with HEX vs existing engines in terms of overall elapsed time and CPU time.

Performance improvements

For HEX, as the data is streamlined in data chunks, the CPU time is the key factore for performance. In addition to performance, we also have tested that it requires much less memory for executing the same statements.

Reduction of memory

You may have noticed that some of your queries have been executing with the newer engine and with Q4 2022 release of SAP HANA Cloud, you will see more statements running by HEX engine. There may be a still few queries running by the existing engines as we don’t have 100% coverage yet but expect them to swith over in 2023.

I will have some followup blogs regarding SQL Plan Stability and SQL Analyzer for HEX.

Best Regards,
Tae Suk Son
SAP HANA Product Management