Where is in-memory actually useful?

First and foremost, SAP has put a lot of effort into positioning its own in-memory database Hana as a revolution among databases.

In the meantime, there are a number of in-memory database solutions for SAP applications, but they all differ. Therefore, as always, it is worth taking a deeper look behind the scenes before a company decides on one of these solutions.

Differences in implementation, usage and actual benefits are very clear, in addition to the initial cost of hardware and software.

Before a company looks at in-memory in a database, the first question is: What do I want to achieve with it?

It is a false expectation that in-memory technology alone, regardless of which manufacturer it is used by, will bring about a fundamental improvement in the performance of all processes from analytical queries or even write transactions in the database. This is not true.

Many requirements - in-memory alone does not solve them

It is not only in-memory that distinguishes a good database. Solutions with an either-or approach do not solve the overarching requirements of a database management system, they provide the solution to individual specific analytical requirements.

A Formula 1 car has not yet been seen at the Dakar Rally. A database is characterized by being able to handle all requirements for performance, high availability, stability, and security in the best possible way, and this with the most effective utilization of existing resources in RAM, CPU, storage, and network.

Especially in the applications of the SAP Business Suite, single-record accesses are recorded with great frequency. If the database system is set up correctly, in-memory can hardly improve the performance of these transactions.

On the contrary, single-record accesses with complex links resolved over several tables in a column-oriented in-memory repository can be significantly more time-consuming than a single-record access to a single database block in memory (buffer cache) in a row-oriented repository.

In an SAP BW system, analytical queries are usually performed, so single-record processing is very rare here. Especially for this use case, in-memory technology can bring a decisive improvement with ever increasing data volumes.

What is the optimum now?

Either 100 percent of all data in memory or only selected objects? Does a database manufacturer decide with a pure in-memory database or does the database administrator decide where in-memory makes sense?

Do we achieve the optimum for all SAP applications, i.e. for SAP BW, SAP ERP, SAP CRM, SAP HR, if we always have to keep all data permanently in memory?

Keeping all data in memory is still associated with high investment costs in suitably equipped hardware with sufficient RAM and CPU for the production and failover environment. It makes more sense to have a solution that allows all possibilities of a performant execution for all read and write transactions.

In the past, all well-known database manufacturers have chosen a line-oriented approach for the development of their databases, because this is the optimal approach for write transactions.

Today we see enormously grown databases. This data must therefore first be created in the databases by means of write transactions. In-memory can hardly provide any support here.

The advantages of in-memory technology clearly lie in the analytical environment when reading and aggregating very large amounts of data.

So there are special use cases in the analytical environment where in-memory technology can actually achieve significant advantages in performance. This suggests that a mix of classic database design with conventional row-oriented storage and the new column-oriented in-memory technology is an optimal approach.

Who should be better informed about performant access paths in its database, if not the database itself? Why should a database system store all data in a column-oriented in-memory architecture if the database determines information for better access via a row-oriented database block based on its statistics?

The introduction of partitions with the physical division and reduction of tables for the purpose of maintaining performance is standard in SAP BW, but very limited in SAP ERP systems and can only be implemented at great expense.

For this reason, scale-out architectures with SAP Hana are also difficult to implement for SAP Business Suite applications. The advantages of massively parallel processing of a single transaction using in-memory via a scale-out can hardly be exploited here.

The approach of Oracle Database In-memory technology is different. It optimally combines both worlds, the classic row-oriented approach for single record processing and in parallel the column-oriented in-memory architecture for extremely accelerated analytical queries.

This gives the database another option for the database optimizer to execute queries from the row-oriented buffer cache or the column-oriented in-memory store with the highest performance for identical tables.

Oracle is thus the only database manufacturer that transparently combines conventional database technologies with the latest in-memory technology for the application.

Many advantages can be derived from this. The database requires much less additional memory compared to a 100 percent in-memory database, since only selected tables are additionally defined with a column-oriented storage in memory.

Analytical requests over larger data sets can now be extremely accelerated. Custom indexes created to speed up analytical queries can be removed again, which can incidentally improve OLTP performance on these objects.

Migration of tables to this column-oriented format is not necessary. All previously used functionalities of the Oracle database such as compression, encryption, Real Application Clusters or backup/recovery can continue to be operated unchanged.

The Oracle database can run on all common operating systems, and the in-memory solution does not change this.

The Oracle database does not know an either-or, in-memory or no in-memory, but a both-as well. Write transactions as well as read transactions on the same tables in a single database are now possible in parallel with traditional methods as well as modern in-memory technology.

This is the basis for the 2015 certification of Oracle Database in-memory technology for all applications based on SAP NetWeaver. This makes Oracle the only manufacturer besides Hana with an in-memory solution that can be used for OLTP and OLAP in SAP.

As a result, a large number of companies were able to very successfully implement existing performance requirements in SAP BW and SAP CRM in just a few days. A significantly improved negotiating position for future hardware investments thanks to vendor diversity and operating system as well as less RAM required also characterize Oracle Database In-memory.

Further technological developments based on Oracle database in-memory technology are now possible with the implementation of "Flat Cubes" as a new design of InfoCubes in SAP NetWeaver BW 7.40. This will not only significantly improve the performance for analyses without the prior complex formation of aggregates, but also the loading performance due to the lack of indices and dimension tables. Pilot customers are being sought for this.