What difference would it make to use CDS View instead of table name in select query?

[sh4il3sh]

I have select queries on mulitple Business Partner related tables, what difference would it make if I select from I_BUSINESSPARTNER instead of BUT000?

Do we have any performance gains or is it just a good practice to do so?

[juliandanho · Accepted solution]

I_BUSINESSPARTNER is a released CDS view (Release Contract C0) in the steampunk technology stack. Selecting from I_BUSINESSPARTNER instead of BUT000 may offer some performance gains depending on the specific scenario and usage. Here are a few factors to consider:

1. Data Model: I_BUSINESSPARTNER is a CDS view, which means it represents a predefined virtual data model that may include joins, projections, and calculated fields. The view is designed to simplify and optimize data access. In contrast, BUT000 is a base table in the underlying database schema. The CDS view might have already incorporated some performance optimizations and aggregated data from multiple tables, resulting in a more efficient query execution plan.
2. Indexing: The underlying tables may have different indexes. CDS views like I_BUSINESSPARTNER can define their own indexes or leverage existing indexes from the underlying tables to enhance performance. These indexes are designed to optimize common access patterns, such as filtering, sorting, and joining. Using I_BUSINESSPARTNER might benefit from such index optimizations.
3. Query Complexity: If your query involves complex joins, filtering conditions, or aggregations, using I_BUSINESSPARTNER might simplify your SQL statement. The CDS view abstracts away the underlying table structure and provides a higher-level interface to access the data. This can make the query more readable, maintainable, and less error-prone.
4. Data Abstraction: I_BUSINESSPARTNER might provide a more business-oriented and semantically meaningful representation of the data compared to the raw tables like BUT000. The view might handle data transformations, conversions, or calculations to present the data in a more intuitive format. This can be beneficial for developers and analysts working with the data.

However, it's essential to note that the performance gains can vary based on the specific use case, database configuration, data volume, and the way the CDS view is implemented. It's recommended to perform performance tests and benchmarks to evaluate the actual impact of using I_BUSINESSPARTNER versus directly querying the BUT000 table in your particular scenario.

In general, using a well-designed and optimized CDS view like I_BUSINESSPARTNER can be considered a good practice as it promotes encapsulation, abstraction, and separation of concerns. It also aligns with the concept of modularization and reusability in software development.

[sh4il3sh · Accepted solution]

Thanks for the explanation, lots of 'might be' here.

[Sandra_Rossi · Accepted solution]

sh4il3sh lot of "might be" but you can trust knowledgeable people, things are always complex under the hood, you will understand after you gain more experience 😉

[amangarg1]

Hi Shailesh,

As per my understanding, difference is in terms of recommended way. When you plan to upgrade, you would see issues if you are firing selects on database tables. You would be all secured during upgrades if you are using released CDS views. Moreover, you have a choice only in classic ABAP. In Embedded Steampunk or Steampunk, you won't have a choice and must use only released CDS views.

Thanks,

Aman Garg

[sh4il3sh]

Thanks for answering Aman, I am worried if would I need additional autorization for those CDS views used in this case?
because I reckon CDS has DCL concept.

[thkolz]

The benefit is that you can access all associations included in the CDS view.
Those associations are only built if you really use them in your ABAP, FIORI etc.

REPORT ztest.

PARAMETERS:
  p_bupa TYPE bu_partner.

SELECT SINGLE
  businesspartner,
  \_currentdefaultaddress-addressid
  FROM i_businesspartner
  WHERE businesspartner = @p_bupa
  INTO @DATA(s_bp).

WRITE s_bp-addressid.

In terms of performance CDS view are not better than selecting from DB tables.
Both CDS views and standard SQL statements generate the same SELECT statements on DB-level in the end.

[lwillnat]

Is there any kind of blog to get more information about your performance statement ?