Advanced SAP HANA Modeling (Part 1): How to Use the Query Results Caching Feature to Improve Performance

  • by Ned Falk, Senior Education Consultant, SAP
  • July 3, 2017
Learn how to best use the query results caching option to improve the performance of your information views. See when to use—or not use—this option as part of your modeled SAP HANA database. Also, learn about other caching topics.
Learning Objectives

Reading this article, you will learn how to:

  • Describe the purpose of query results caching and the new invalidation period on SAP HANA information views
  • Understand the technical underpinnings for query results caching
  • Understand the configuration to enable the query results caching feature
  • Weigh the potential costs for delayed visibility of the changes in data when caching is used
Key Concept
For many years, SAP HANA has offered the ability to cache information view results. As of Support Package Stack 9, however, some better options became available and, with the release of Support Package Stack 11, there are even more. Caching information view results improves performance, but can add latency, causing the data to be outdated. Knowing how and when to enable caching features is a very important concept for modelers to understand so they can apply the caching feature when needed.

With each new release of SAP HANA come more features, many of which target improved performance of information views. This is especially true in caching. For a basic modeler who does not write code, SAP HANA Support Package Stack 9 brings many new options for how to best and more easily use query results. In this article, I show how to use this feature in the context of a view that uses smart data access and remote/virtual tables, as this is the primary use case. In addition, I discuss the performance advantages of using the daily and hourly query cache invalidation feature, as well as the drawbacks of these options when it comes to memory consumption and data latency (looking at old data). Finally, learn about a new option with Support Package Stack 11, the so-called transactional cache invalidation option, available with Support Package Stack 11 and newer. This is my preferred option, as it removes the latency drawback.

Note
The focus of this article is on the results cache. There is an even newer feature called the query view cache, available in Support Package Stack 11, but this requires code and is beyond the scope of this article. It will, however, be discussed in more detail in a future article.

Definition, Background, and History of the Query Results Caching Feature

With the introduction of data warehousing systems came extract, transform, and load (ETL) tools that extracted data to the warehouse where it was stored physically in optimized structures for reporting, most often in a star schema. Given the state of reporting tools of the day, the reports on this summarized structured data were produced quickly. However, compared to the speed of today’s reporting systems, the evaluation of how fast a report is produced is measured in seconds, versus the minutes and hours this process took in the past. Even complex reports that connect many tables are now expected to run in seconds. SAP HANA itself, with all its memory, columnar data, and parallel processing, meets 99 percent of this need, but what about the +/– one percent of report processing that is still considered too slow? The new SAP HANA query results caching feature addresses this +/– one percent.

Ned Falk

Ned Falk is a senior education consultant at SAP. In prior positions, he implemented many ERP solutions, including SAP R/3. While at SAP, he initially focused on logistics. Now he focuses on SAP HANA, SAP BW (formerly SAP NetWeaver BW), SAP CRM, and the integration of SAP BW and SAP BusinessObjects tools. You can meet him in person when he teaches SAP HANA, SAP BW, or SAP CRM classes from the Atlanta SAP office, or in a virtual training class over the web. If you need an SAP education plan for SAP HANA, SAP BW, BusinessObjects, or SAP CRM, you may contact Ned via email.

See more by this author


Comments

No comments have been submitted on this article. 


Please log in to post a comment.

To learn more about subscription access to premium content, click here.