In-Memory Computing
Improved hardware economics and software innovations have made it possible for SAP to deliver on its vision of the real-time enterprise with in-memory business applications.
The Past: Disk-Centric, Singular Processing Platforms
Increased data volume causes major bottlenecks in data transfers. For example, input/output (I/O) transfer rates from storage disks to servers have not kept up with data volumes. Disk-centric computing creates significant bottlenecks in data management. As a result, users experience slowed down online transactions and batch processes.
To overcome performance bottlenecks in the past, IT systems have become very complex decentralized architectures. This decentralized design has compromised business user flexibility, and added significant costs to keep all the business data synchronized.
The Present: Low Latency Computing Driven by In-Memory Technology
Keeping all the business data in one central location, and available for all business users 24x7 is the solution, but it was not possible until the modern hardware became available. This modern hardware architecture enabled in-memory computing, and SAP HANA was built from the ground up to support this new architecture.
Row Store Versus Column Store
SAP HANA is an ACID-compliant, in-memory database. ACID is an acronym that means the database can support Atomicity, Consistency, Isolation, and Durability. This is a primary requirement of a database, which ensures that it is 100% reliable for mission-critical applications. The database must guarantee data accuracy and integrity even when there are lots of simultaneous updates across multiple tables.
The traditional database systems (without in-memory technology) focus on one workload: OLTP or OLAP. With SAP HANA, this has changed because it handles transactional and analytical workloads very well. The SAP HANA database stores the data in a columnar way, therefore organizing the data in DRAM in an optimal way for the CPU to access.
Queries from analytical applications that are sent to the database often require only a subset of the overall data in the table. Usually, only a limited number of columns are required. With the column store, only the required columns are searched, so you avoid unneeded search activity in the memory.
With column store, SAP HANA scans columns of data so quickly that additional indexes are usually not required. This helps to reduce complexity by avoiding the need to constantly create, drop, and rebuild indexes. Column store tables are optimal for parallel processing, as each core is able to work on a different column.
The column store is seen as optimal for analytical processing (OLAP), but with the inclusion of the delta store, the column store also provides optimal performance for transactional processing (OLTP). A delta store is added to every column store table and is write-optimized. In this way, a columnar table is fast for read and write operations.
Column- and Row-store Tables in SAP HANA
The SAP HANA database supports both column-based and row-based tables. However, the table storage is optimized for column tables. When creating a table, you need to decide whether the table should be column- or row-store, depending on the use case.
Use a column table when:
- Performing aggregations on a small number of columns.
- Performing searches based on the values in only a few columns.
- The table has a large number of columns.
- The table has a large number of rows and mostly columnar operations are performed.
- Achieving a high column compression rate is required.
Tables that are stored in the column-store are read-optimized. These tables have better compression rates than tables stored in the row store. Furthermore, some features of the SAP HANA database, such as partitioning, are available only for column tables.
Column-based storage is used for large tables with bulk updates. However, in a compressed column table the update and insert performance is slower than on row tables. SAP has addressed this slower update and insert of columnar tables by introducing a delta store. Every table has a write-optimized uncompressed delta store. This makes columnar tables have fast read and write capabilities.
You can join row tables with column tables in the SAP HANA database. However, it is more efficient to join tables of the same storage type.
Use a row table when:
- You want to process single records at once, or many selects and updates.
- You want to access complete records.
- Columns contain mainly distinct values.
- You do not need aggregations or fast search.
- The number of rows is small.
Row-store tables are mostly small tables that have frequent, single updates. Row-store tables will be completely loaded into memory at the start of the SAP HANA database. On row-store tables you will find indexes to improve the read performance. The indexes are recreated at every start of the SAP HANA database, so they are always optimal.
You can change an existing table from one storage type to the other (ALTER TABLE ALTER TYPE).
