Row Store vs Column Store in SAP HANA

The SAP HANA database allows you to create your tables in Row or Column Store mode. In this blog, I will demonstrate that each method has its advantages and disadvantages and should be used for specific cases.

Thanks to two kind of tests, I will show you that the Row Store mode should be used for simple SELECT SQL queries, without aggregation and the Column Store mode should be used for complex SELECT queries, containing aggregation levels.

If you want to have more information regarding the Column Store or the In-memory technologies, don’t hesitate to assist at the next dbi services event:
https://www.dbi-services.com/newsroom/events/

Test 1: Simple SELECT query

Goal of the tests

This test will show you the difference of performance using a Row Store and a Column Store table in a simple SQL query.

Description of the test

A SELECT query will be send to the database and we will check the Server time response.

SQL Query

Using a Row Store table

The SQL is the following:

Using a Column Store table
The SQL is the following:

Tables

Row Store Table

You can find here information regarding the Row Store table used in the test.

Name:                 SALES_ROW
Table type:          Row Store
Row count:         10 309 873
Index:                1
Partition:            0 (SAP HANA doesn’t allow the possibility to create partition on Row Store table)

Column Store Table

You can find here information regarding the Column Store table used in the test.

Name:                  SALES_COLUMN
Table type:           Column Store
Row count:          10 309 873
Index:                 0 (SAP HANA automatically apply a index if it is need)
Partition:             1 RANGE partition on CUST_ID

Result of the test

Using the Row Store table

Using the Column Store table

Test 2: Complex SELECT query

Goal of the tests

This test will show you the difference of performance using a Row Store and a Column Store table in a complex SQL query.

Description of the test

A SELECT query will be send to the database and we will check the Server time response.

SQL Query

Using a Row Store table

The SQL is the following:

Using a Column Store table
The SQL is the following:

Tables

Row Store Fact Table

You can find here information regarding the Row Store table used in the test.

Name:                  SALES_ROW
Table type:          Row Store
Row count:         10 309 873
Index:                   2
Partition:             0 (SAP HANA doesn’t allow the possibility to create partition on Row Store table)

Column Store Fact Table

You can find here information regarding the Column Store table used in the test.

Name:                  SALES_COLUMN
Table type:          Column Store
Row count:         10 309 873
Index:                   0 (SAP HANA automatically apply a index if it is need)
Partition:             1 RANGE partition on CUST_ID

Result of the test

Using the Row Store tables

Using the Column Store tables

Conclusion

Row and Column store modes in SAP HANA should be used in two different contexts:

• Tables in Row store mode must be used in SELECT queries WITHOUT any aggregation functions
• Tables in Column store mode are powerful when they are used to create analytical queries or view, using aggregation functions (GROUP BY, …)

The performance can be highly optimized if the tables selected in the queries have the right store mode.