Skip to Content
Technical Articles
Author's profile photo Andrea Schlotthauer

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

As of ABAP release 7.91, 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 ABAP release 7.91
  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 ABAP release 7.91
  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 joined data source (right data source). For example, a source cardinality of one means that each record of the joined data source has zero or one matching entries in the original data source. The target cardinality specifies the number of matching records for each record of the original data source (left data source). A target cardinality of many means that each record of the original data source has multiple matching entries in the joined data source.

2. What’s new in a nutshell

Before release 7.91

As of release 7.91

Cardinality specification in ABAP CDS and SQL

Users can define the target cardinality of associations and joins. Users can define the source and target cardinality of associations and joins.

Translated into HANA cardinalities

  • ONE
  • MANY
  • ONE
  • MANY
  • EXACT ONE

Optimization opportunity

  • Right-hand pruning
  • Right-hand pruning
  • Left hand pruning
  • Rewrite left outer join to inner join for optimized HANA execution plans.

3. Cardinality syntax before ABAP release 7.91

Before ABAP release 7.91, 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 left table, scarr in this example, has multiple matching records in the right table, 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 right-hand data source, the right-hand data source 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 left data source, mara in this example, are selected. The SQL Optimizer therefore prunes the right table, 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%20wrong%20cardinality%20can%20lead%20to%20a%20wrong%20row%20count

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 since ABAP release 7.91

As of ABAP release 7.91, 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 left data source if no field from the left side 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 left side 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. left side), but since it is specified in the ON-condition as a 1:1 relation, this field is simply taken from the right side 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 left data 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 left-hand data source can have zero or one entries in the right-hand data source.

With the newly available target cardinality of EXACT ONE, it is clear that there is exactly one record on the right side.

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 on the right side, 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 on the right side.

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:

Optimization opportunity Join type
Left-hand pruning
  • inner exact one to many
  • left outer exact one to many
Right-hand pruning
  • inner many to exact one
  • left outer many to one
Right-hand pruning & rewrite to inner join
  • left outer many to exact one
Left & right hand pruning
  • inner exact one to exact one
  • left outer exact one to one
Left & right hand pruning & rewrite to inner join
  • left outer exact one to exact one

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

Assigned Tags

      7 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Alejandro Sensejl
      Alejandro Sensejl

      What SAP ERP or SAP S/4HANA version is required to use 7.91 release?

      Author's profile photo Andrea Schlotthauer
      Andrea Schlotthauer
      Blog Post Author

      S/4HANA 2302

      SAP_BASIS 7.58 (next big on-prem shipment)

      ABAP Platform 2023 (next shipment)

      Author's profile photo Mio Yasutake
      Mio Yasutake

      Thanks for this information. With below syntax, source cardinality one (or exact one) would be expected, and we think of cardinality as "how many entries exist in the target data source for each record of the source entity".

      association [<min>..<max>]

      With the new syntax, in which situation should we use the source cardinality "many"? Especially, I would like to know a practical use case of "many to many".

      Author's profile photo Andrea Schlotthauer
      Andrea Schlotthauer
      Blog Post Author

      The syntax

      association [<min>..<max>]

      specifies only the target cardinality. The source cardinality is implicitly set to MANY by default.

      You can, for example, specify that each record of the left data source has exactly four matching records in the right data source. [1..4]. When sent to the database, this is translated to MANY.

      A many-to-many relationship exists, for example, between customers and products: customers can purchase various products, and products can be purchased by many customers.

      Does that answer your question?

      Author's profile photo Mio Yasutake
      Mio Yasutake

      Thanks for for your reply. I am confused about the words "source" and "target". In the following example, the main data source is salesorder and salesorderitem is associated. These entities have on to many relationship. 

      define view entity DEMO_SALES_ORDER
        as select from salesorder 
        association [0..*] to salesorderitem as _item on _item.salesorder = salesorder.salesorder 

      Could you verify if my understanding is correct?

      1. In above case, salesorder is the target and salesorderitem is the source.
      2. "target cardinality" in this case means, based on the target data source (salesorder), how many matching entries exist in salesorderitem, thus "many".
      3. "source cardinality" is implicitly set to many, however with the new syntax this should be "exact one". 
      Author's profile photo Andrea Schlotthauer
      Andrea Schlotthauer
      Blog Post Author

      I know it is confusing, it took me some time to understand as well.

      1. In the example above, salesorder is the association source (left data source).
      2. salesorderitem is the association target (right data source).
      3. The cardinality is many to many, because the source cardinality is set to MANY automatically and the target cardinality is set to MANY explicitly. [0..*] means that each sales order can have any number (between 0 and any) of sales order items. Therefore, many-to-many.
      4. With the new cardinality syntax, the cardinality should be set to "EXACT ONE TO MANY" in my understanding:
      • Each sales order item belongs to exactly one sales order (source cardinality of exact one)
      • Each sales order can have many sales order items (target cardinality of many)

      SAP Community, SAP Help Portal and even Goolge can help, there are many source that explain the concept of cardinaliy....

      Author's profile photo Mio Yasutake
      Mio Yasutake

      Thanks for your quick reply. I had misunderstood the meanings of "source" and "target" in reverse.

      "left data source" and "right data source" are easier for me to understand.