Skip to Content
Technical Articles
Author's profile photo Tae Suk Son

Faster query execution using lesser memory in SAP HANA Cloud

Happy New Year!

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



Assigned Tags

      You must be Logged on to comment or reply to a post.
      Author's profile photo Jens Gleichmann
      Jens Gleichmann

      Hi Tae Suk Son ,


      thanks for providing this statistics and benefits of the HEX. It is the right direction using HEX for more scenarios.

      As you already mentioned is HEX nothing new and it took a long time to get it stable. Everyone can have a look at the history and the known issues of HEX in note 2570371.

      However, what is currently not transparent are the well-known problems of the engine in the context of SAP HANA Cloud. As you can see there are defined fixes for the on-prem variant HANA 2.0 but not for HANA cloud (HANA 4.0). Is it possible to add this information to the notes? For instance the latest known issue of HEX (3259146) is fixed in Rev. 2.00.66, but is HANA cloud also affected?




      Author's profile photo Tae Suk Son
      Tae Suk Son
      Blog Post Author

      Hi Jens,


      Before replying to your question, I would like to mention that we have a different strategy between SAP HANA Cloud and SAP HANA 2.0. Regarding HANA Cloud, we are more aggressive to having HEX function as a single engine hopefully replacing the old engines early as possible. For HANA 2.0, we are a bit conservative not to introduce regressions due to HEX. So, with Q4 2022 release, HEX is fully active while HANA 2.0 has a threshold and will fallback to the old engine when the resultset is bigger than the threshold.

      Additionally, we are introducing new HEX features in HC and then downporting them to HANA 2.0 with fallback. So, if there was a fix in HANA 2.0, then the fix is already been applied to HANA Cloud first.

      The note 2570371 is KBA maintained by Martin Frauendorfer. I'm not sure if the scope of KBA includes HANA Cloud as well. Let me reach out to him to clarify.

      Best regards,