From Open SQL to CDS Views: Rewriting SAP Data Access for Performance at Scale

Modern SAP landscapes running on SAP HANA demand a rethink of how ABAP programs access data. Traditional Open SQL queries embedded in ABAP code have served developers for decades, but at large data volumes, they can become performance bottlenecks. SAP’s introduction of Core Data Services (CDS) views offers a new paradigm: push more work to the in-memory database and retrieve only what’s needed. 

Traditional ABAP Data Access With Open SQL

Open SQL is the standard SQL interface in ABAP that allows developers to query the underlying database in a database-agnostic way. For example, an ABAP report might join two tables and fetch results like this:

SELECT bkpf~bukrs, bkpf~belnr, bkpf~gjahr, 
       bseg~koart, bseg~wrbtr, bseg~shkzg
  FROM bkpf 
  INNER JOIN bseg ON bkpf~bukrs = bseg~bukrs 
                 AND bkpf~belnr = bseg~belnr 
                 AND bkpf~gjahr = bseg~gjahr
  INTO TABLE @DATA(it_fi_docs)
  WHERE bkpf~bukrs = '1000' 
    AND bkpf~gjahr = '2023' 
    AND bseg~koart = 'K'.

This Open SQL example joins the BKPF and BSEG tables to retrieve financial documents. Open SQL sends such queries to the database, and on SAP HANA, the heavy lifting of the join and filtering is done in-memory on the DB server. The result is then brought back to the ABAP application server.

However, the challenge with Open SQL at scale comes when ABAP code handles large data sets or complex logic in the application layer. Common performance issues in legacy ABAP include:

• Too much data transferred: Selecting wide tables or not filtering enough leads to heavy network and memory usage. Best practice is to filter and aggregate in the query to keep the result set small and transfer only the required columns (avoid SELECT *).

• Multiple round-trips: Performing calculations with many small queries or loops causes repeated DB calls. It’s more efficient to push joins and subqueries into one SQL if possible. Each context switch adds overhead.

• Application-side processing: If business logic runs on millions of records in ABAP, the application server CPU becomes the bottleneck. The database could perform these operations faster, set-wise.

In summary, while Open SQL can express complex data retrieval, ABAP developers traditionally had to be very disciplined in query design to avoid performance issues at scale. This paved the way for a new approach leveraging SAP HANA’s strengths.

The Case for Change: Code-to-Data Paradigm

SAP HANA’s in-memory, columnar architecture enables it to execute aggregations, filters, and joins extremely fast at the database level. To exploit this, SAP advocated the code-to-data paradigm. push computations down to the database rather than pulling data up to the code. Rewriting data access using CDS views is a key technique in this paradigm, alongside others like AMDP.

By offloading heavy operations to the DB, we minimize data transfer and let HANA’s optimized engines handle crunching the data. For example, instead of reading a full table and then filtering in ABAP, you pass WHERE conditions so the DB does it. Instead of multiple selects and merges in ABAP, you perform a JOIN or a subquery in one shot.

Another driver for change is SAP’s new data models in S/4HANA. Many classic transparent tables were replaced by HANA-optimized structures or compatibility views. Custom ABAP code written for ECC often breaks or needs adaptation for S/4HANA’s simplified data model. 

In these cases, SAP often provides CDS views as the new interface to data. As one DZone article notes, engineers moving to S/4 must switch to the S/4 equivalents to replace old data access logic. In short, adopting CDS views is not only about performance but also about aligning with SAP’s modern architecture.

Introducing ABAP Core Data Services (CDS) Views

ABAP CDS is a framework to define rich data models directly on the database, using a declarative syntax in ABAP Development Tools (ADT). A CDS view is essentially a view in the HANA database, defined via an ABAP DDL statement. For example, here’s a simple CDS view definition joining two tables:

@AbapCatalog.sqlViewName: 'ZDEMO_FLIGHTS'
define view ZFlightInfo as
  select from spfli 
    inner join scarr on spfli.carrid = scarr.carrid
  {
    scarr.carrname  as carrier,
    spfli.connid    as flight,
    spfli.cityfrom  as departure,
    spfli.cityto    as arrival
  }

This CDS view ZFlightInfo performs the same join between SPFLI and SCARR as an equivalent Open SQL join would. In fact, you could copy-paste the join logic from ABAP into the CDS definition with minor syntax changes. After activating this view in ADT, the system creates a database view in HANA. ABAP programs can then consume the CDS view just like a table:

SELECT * FROM ZFlightInfo 
  INTO TABLE @DATA(it_flights)
  ORDER BY carrier, flight.

The result set it_flights from the CDS view will be identical to what an Open SQL join would produce for the same input tables. Under the hood, both approaches result in the database executing a similar SQL SELECT. So, why use CDS? The benefits become evident as complexity grows:

• Reusability and model centralization: CDS definitions are stored in the ABAP Dictionary and can be reused by any number of programs or even other CDS views. Instead of writing the same joins or calculations in multiple ABAP reports, you define them once in a CDS view. SAP recommends using a CDS view when you need to retrieve data from multiple related tables, because it involves the least amount of coding and can be reused in multiple objects. In large-scale systems, this consistency is key to a single source of truth for that piece of data logic.

• Rich expression and metadata: CDS supports advanced SQL features and built-in functions. You can define calculated fields, aggregations, and even leverage specialized HANA capabilities within the view. CDS also allows adding annotations, making the data model self-descriptive.

• Performance through pushdown: By moving logic into the CDS (and thus into SQL on the database), you reduce the workload on the ABAP layer. The database can apply filters, joins, and computations in parallel, using its optimized engines. Only the final result is sent back to ABAP. 

• Secure and controlled access: CDS views integrate with the SAP authorization concept, ensuring consistent enforcement of business security rules at the data model level, rather than scattering checks in ABAP code. This means performance benefits without sacrificing governance.

Tutorial: Converting an Open SQL to a CDS View (with Code)

To solidify the concept, let’s walk through a simple conversion. Imagine we have an ABAP report that needs to list flight routes with the airline name. In classic ABAP, you might do this with an inner join in Open SQL as shown below:

Open SQL Approach (Legacy ABAP code):

DATA: lt_flights TYPE TABLE OF zflight_info. "Structure for results

SELECT scarr~carrname AS carrier,
       spfli~connid  AS flight,
       spfli~cityfrom AS departure,
       spfli~cityto   AS arrival
  FROM spfli
  INNER JOIN scarr ON spfli~carrid = scarr~carrid
  INTO TABLE @lt_flights
  ORDER BY carrname, connid.

This code joins SPFLI with SCARR and populates an internal table lt_flights. It works, but the logic is embedded in the program. Now, suppose we want to reuse this same join in multiple places. We can refactor it into a CDS view:

CDS View Approach:

Define the view in ABAP DDL (e.g., in Eclipse ADT):

@AbapCatalog.sqlViewName: 'ZFLIGHTINF'
@AccessControl.authorizationCheck: #NOT_REQUIRED
define view ZFlightInfo as
  select from spfli
    inner join scarr on spfli.carrid = scarr.carrid
  {
    scarr.carrname  as carrier,
    spfli.connid    as flight,
    spfli.cityfrom  as departure,
    spfli.cityto    as arrival
  }

We give the view a name ZFlightInfo. Note that this is almost identical to the Open SQL, just expressed as a view definition. Once activated, the CDS is available system-wide. Now our ABAP report can simply do:

SELECT * FROM ZFlightInfo 
  INTO TABLE @lt_flights
  ORDER BY carrier, flight.

The result in lt_flights will be the same. We have effectively decoupled the data retrieval logic from the program and centralized it in the DB layer. This not only improves reuse; in a HANA system, it can also improve performance. The database can better optimize a single persistent view than ad-hoc SQL scattered in code. And if we needed to adjust the join or add a new field.

Performance Considerations and Best Practices

When rewriting Open SQL to CDS, ABAP developers should keep a few important considerations in mind:

• Measure, don’t guess: Simply converting an Open SQL to a CDS view doesn’t magically speed up the query if it was already efficient. As noted earlier, for straightforward SELECTs or joins, the performance will be equivalent in many cases. The real gains come when you use CDS to do more complex processing in one go. Always use tools like ST05 SQL trace or HANA’s PlanViz to ensure the new design is actually optimal. The execution plan is what matters, not whether you wrote it in Open SQL or CDS.

• Avoid over-complex views: It’s possible to go overboard with stacking CDS views on top of each other. While layering is good for separation of concerns, too many nested views or excessive use of associations can lead to very complex SQL at runtime. This can confuse the optimizer or prevent predicate pushdown. Be wary of heavy calculations in a single CDS. If performance suffers, consider alternatives like ABAP Managed DB Procedures (AMDP) for really complex logic or break the problem down differently.

• Select only what you need: Just as with Open SQL, a CDS view should be designed to return only necessary fields and records. Don’t define a CDS with SELECT * from a wide table list the needed fields. This ensures consumer queries aren’t unknowingly pulling extra data. One common pitfall is using CDS to expose an entire table with all columns, which defeats the purpose. Instead, tailor views to use cases or use parameters in CDS to filter data.

• Use CDS features wisely: Leverage CDS capabilities like aggregations, calculated fields, and unions to eliminate extra work in ABAP.

• Reuse and consistency: Replace multiple Open SQL implementations of the same logic with a single CDS. Not only does this reuse improve maintainability, but it also means the database might handle the unified load more efficiently. SAP itself follows this approach in S/4HANA with the Virtual Data Model, hundreds of CDS views that serve as the source for Fiori apps and reports, rather than raw table access. By moving to CDS, you align your custom code to the same philosophy.

Conclusion

Rewriting data access from Open SQL to CDS views is a strategic move for ABAP developers aiming to maximize performance at scale. By pushing more logic to the SAP HANA database, we take full advantage of its in-memory speed and parallel processing. CDS views enable complex data gathering in one shot, reduce the load on the application server, and provide a modular, reusable data model for your SAP applications. 

That said, an engineer must also approach CDS with a critical eye, understanding the execution plan and ensuring that moving to CDS truly improves the situation, rather than blindly adding abstraction. Advanced ABAP development is about choosing the right tool for the job. In the case of data-intensive operations, CDS views have proven to be a powerful tool, aligning with SAP’s modern direction and delivering robust performance at scale. By rewriting your data access with CDS and following best practices, you can future-proof your ABAP code for the HANA era, achieving faster results and a cleaner, more sustainable codebase for the long run.