New Cardinality Syntax for Performance Optimization in ABAP CDS and ABAP SQL

As of release 2302, a new syntax is available for specifying the cardinality of associations and joins in ABAP CDS and ABAP SQL. When used in the right way, it speeds up the query performance on the SAP HANA database. This blog post explains the new cardinality syntax, how it can improve query performance, and everything else you need to know about cardinalities in ABAP.

Contents:

1. Introduction
2. What’s new in a nutshell
3. Cardinality syntax before SAP BTP, ABAP Environment 2302
4. Optimization opportunity: right-hand pruning
5. Risks of a wrong cardinality specification
6. Syntax check warnings in ABAP CDS help users spot wrong cardinality specifications
7. New syntax since SAP BTP, ABAP Environment 2302
8. New optimization opportunities
9. Availability
10. Conclusion
11. Further Information

———————————————————————————————————————–

1. Introduction

First of all, what does cardinality mean? In a join of two SQL data sources, the cardinality expresses the relationship between the rows of the data sources. Typical cardinalities are one-to-one (1:1), one-to-many (1:n), many-to-one (n:1), and many-to-many (n:m).

A distinction is drawn between source cardinality and target cardinality.  The source cardinality describes the number of matching records for the entries of the join target. For example, a source cardinality of one means that each record of the join target has zero or one matching entries in the join source. The target cardinality specifies the number of matching records for each record of the join source. A target cardinality of many means that each record of the join source has multiple matching entries in the join target.

The following syntax clarifies what is meant by join source and join target. The join source is the <source_view> and the join target is the <target_view>.

define view entity <source_view>
  as select from <table>
association to <target_view>
 on <source_view>.<element> = <target_view>.<element>

2. What’s new in a nutshell

3. Cardinality syntax before SAP BTP, ABAP Environment 2302

Before SAP BTP, ABAP Environment 2302, the cardinality of CDS associations was specified in square brackets and in numbers. Only the target cardinality could be specified with the following syntax pattern:

association [<min>..<max>] to AssocTarget as AliasName on CdsCond

Example:

association [1..*] to spfli as _spfli on _spfli.carrid = scarr.carrid

[1..*]  means that each record in the association source, scarr in this example, has multiple matching records in the association target, spfli in this case. This cardinality is translated to TO MANY on the SAP HANA database. In addition, the source cardinality is set to MANY by default. When the association is used in a path expression, it is instantiated in native SQL and the SQL statement generated on the database generates a left outer many-to-many join.

4.Optimization opportunity: right-hand pruning

A target cardinality of TO ONE is used by the SQL Optimizer for performance optimization by suppressing surplus joins. The optimizer decides whether a join must be executed or whether it can be omitted without affecting the correctness of the data. For example, in a left outer many-to-one join, if a query does not select any field from the join target, the join target is pruned.

Here’s an ABAP SQL example:

WITH
+m AS ( SELECT mandt, matnr, ernam FROM mara ),
+t AS ( SELECT mandt, matnr, maktx AS mattext FROM makt WHERE spras = 'E' )
SELECT FROM +m 
LEFT OUTER MANY TO ONE JOIN +t ON +m~mandt = +t~mandt AND +m~matnr = +t~matnr
FIELDS +m~mandt, +m~matnr, +m~ernam
ORDER BY +m~matnr
INTO TABLE @FINAL(result).

In this SELECT statement, only fields from the join source, mara in this example, are selected. The SQL Optimizer therefore prunes the join target, maktx in this example. The HANA DB Studio offers tools such as the HANA explain plan where this pruning becomes obvious.

5. Risks of a wrong cardinality specification

However, caution is advised. The cardinality specification should match the data in question. That means, for example, if an airline offers multiple flights, but each flight is assigned exactly one airline, the cardinality should be specified as ONE TO MANY. Otherwise, unexpected behavior may occur. Here’s an ABAP CDS example:

The following CDS view entity does not explicitly specify a cardinality for the association _spfli. Therefore, the cardinality is implicitly set to the default cardinality many-to-one.

@AccessControl.authorizationCheck: #NOT_REQUIRED 
define view entity DEMO_CDS_WRONG_CARDINALITY_1 
  as select from scarr 
  association to spfli as _spfli on _spfli.carrid = scarr.carrid 
{ 
  key scarr.carrid   as carrid, 
      scarr.carrname as carrname, 
      _spfli.connid  as connid 
}

Two SELECT statements are issued on the CDS view and they return a different number of rows.

SELECT *
  FROM demo_cds_wrong_cardinality_1
  INTO TABLE @DATA(itab).

DATA(rownumber) = sy-dbcnt.

SELECT COUNT(*)
  FROM  demo_cds_wrong_cardinality_1
  INTO  @DATA(count).

The two reads return a different number of rows. This shows that the cardinality should always be defined explicitly to match the data in question to avoid unexpected behavior.

A wrong cardinality can lead to a wrong row count

This example is taken from the ABAP Keyword Documentation in the topic about cardinalities.

6. Syntax check warnings in ABAP CDS help users spot wrong cardinality specifications

The ABAP CDS editor in ADT throws a syntax check warning if the target cardinality does not match the data in question.

The syntax checker checks the key elements of the association target. If all key elements of the association target are compared with an EQUAL operator with an element in the association source, the target cardinality should be to-one. Otherwise, it should be to-many.

And here’s another syntax warning regarding cardinalities that you may have come across:

This warning is issued if an association is used in a path expression.

define view entity CardinalityWarning
as select from spfli
association of many to many sflight as _Flights 
  on $projection.carrid = _Flights.carrid
  and $projection.connid = _Flights.connid
{
key carrid,
key connid,
_Flights.fldate
}

The path field increases the result set in this case, as shown in the following screenshot:

7. New syntax as of SAP BTP, ABAP Environment 2302

As of SAP BTP, ABAP Environment 2302, the following new cardinality syntax written in words is available in ABAP CDS and ABAP SQL:

[many | one | exact one ] to [ many | one | exact one ] 

Example: CDS association with cardinality written in words

association of many to many spfli as _spfli on _spfli.carrid = scarr.carrid

Example: SQL join with cardinality written in words

LEFT OUTER EXACT ONE TO ONE JOIN makt ON mara~matnr = makt~matnr

This new syntax allows you to specify a source cardinality  (not just a target cardinality), and it provides the cardinality of EXACT ONE besides MANY and ONE. This information opens up further opportunities for join pruning and optimization.

The syntax written in words is the recommended option. For reasons of downward compatibility, the old syntax is still supported and it won’t be declared obsolete in the near future.

8. New optimization opportunities

Source cardinality of EXACT ONE: left-hand pruning

A source cardinality of EXACT ONE allows the SQL Optimizer to prune the join source if no field from the join source is used.

Here’s an example:

CDS view entity for material (Basic view):

EndUserText.label: 'view entity, cardinality'
define view entity material
as select from mara
{
key matnr,
ernam
}

CDS view entity for material text (Basic view):

@EndUserText.label: 'view entity, cardinality'
define view entity materialText 
as select from makt
{
key matnr,
maktx as mattext
}
where spras = 'E'

Composite view that combines the material and the material text:

@EndUserText.label: 'view entity, cardinality'
define view entity LeftHandPruning 
as select from Material as a
association of exact one to one MaterialText as _b on $projection.matnr = _b.matnr 
{
key matnr,
_b.mattext
}
where _b.mattext like 'A%'

In this example, the association source is pruned in the HANA join. The data from the material view is not processed in the HANA SQL query. The field matnr seems to come from the material view (i.e. association source), but since it is specified in the ON-condition as a 1:1 relation, this field is simply taken from the association target and the result remains the same.

The SAP HANA Explain Plan might look as follows:

It can be seen that no join is instantiated on the SAP HANA database.

So a source cardinality of EXACT ONE allows for left-hand pruning as long as no field is picked from the association source. Text associations are a typical use case.

Caution: This optimization opportunity is currently only available in SAP HANA Cloud systems. It will probably be made available in SAP HANA on-premise systems in the future.

Target cardinality of EXACT ONE: right-hand pruning and rewrite to inner join

With the old syntax, a target cardinality of TO ONE was available. A target cardinality of TO ONE means that each record of the join source can have zero or one entries in the join target.

With the newly available target cardinality of EXACT ONE, it is clear that there is exactly one record in the join target.

For the SQL Optimizer, this means that the join can be rewritten to an inner join. Because if you can rely on the fact that there is a record in the join target, you can as just well execute it as an inner join, because you don’t have to consider cases where you don’t find a record in the join target.

The following two queries are treated identically, even though the define different join expressions:

ABAP SQL statement with left outer join:

WITH
+m AS ( SELECT mandt, matnr, ernam FROM mara ),
+t AS ( SELECT mandt, matnr, maktx AS mattext FROM makt WHERE spras = 'E' )
SELECT FROM +t
LEFT OUTER ONE TO EXACT ONE JOIN +m ON +m~mandt = +t~mandt AND +m~matnr = +t~matnr
FIELDS +t~matnr, +t~mattext, +m~ernam
ORDER BY +t~matnr
INTO TABLE @FINAL(result).

ABAP SQL statement with inner join:

WITH
+m AS ( SELECT mandt, matnr, ernam FROM mara ),
+t AS ( SELECT mandt, matnr, maktx AS mattext FROM makt WHERE spras = 'E' )
SELECT FROM +t
INNER ONE TO EXACT ONE JOIN +m ON +m~mandt = +t~mandt AND +m~matnr = +t~matnr
FIELDS +t~matnr, +t~mattext, +m~ernam
ORDER BY +t~matnr
INTO TABLE @FINAL(result1).

On the SAP HANA database, both queries are treated identically. The join is in both cases instantiated as inner one-to-one join. This rewriting of a left outer join can lead to an optimized execution plan and accelerate the query performance massively.

Overview of optimization opportunities

The following table lists all optimization opportunities:

association [1] to <target> 
  on <cds_cond>

For more details on join pruning, see the SAP HANA Performance Guide for Developers under Join Cardinality | SAP Help Portal.

9. Availability

The new cardinality syntax written in words is available for ABAP CDS associations, CDS joins, in CDS path expressions, and also for SQL joins, SQL path expressions,  and CTE associations.

10. Conclusion

• Remember to always specify the cardinality for your associations and joins. This can significantly improve the query performance.
• Be careful to use the correct cardinality or you may encounter unexpected results.

11. Further information

• ABAP Keyword Documentation, Cardinality
• Join Cardinality | SAP Help Portal
• Introduction of Join Cardinalities In SQL With SAP HANA 2.0 SPS04 | SAP Blogs