S/4HANA Query Optimizer

How to Use the S/4HANA Query Optimizer

1. Enter your SQL query in the Query Optimizer tab.
2. Click "Optimize" to transform your query for optimal S/4HANA performance.
3. Review the results and recommended changes.
4. Save your optimized query for future reference.

Key Features

• Subquery Optimization: Flattens and optimizes subqueries for better performance.
• Join Reordering: Reorders joins for optimal execution.
• CDS View Replacement: Replaces standard tables with optimized CDS views.
• Index Hints: Adds database hints for better query execution.
• Client Field Management: Automatically adds client fields when necessary.

Table Replacements

Legacy tables are replaced with their equivalent CDS views for better performance and integration with S/4HANA analytics.

Join Optimizations

The optimizer reorders joins based on table cardinality and filter conditions to ensure the most efficient execution path.

Subquery Handling

Nested subqueries are flattened when possible to improve performance. Correlated subqueries are transformed when beneficial.

Index Hints

Appropriate index hints are added to guide the database optimizer for better execution plans.

Example Optimization

-- Original Query
  SELECT a.vbeln, a.erdat, b.posnr, b.matnr, c.maktx
  FROM vbak a
  JOIN vbap b ON a.vbeln = b.vbeln
  JOIN makt c ON b.matnr = c.matnr AND c.spras = 'E'
  WHERE a.erdat > '20250101'
  
  -- Optimized Query
  SELECT a.SalesOrder, a.CreationDate, b.SalesOrderItem, 
         b.Material, c.MaterialName
  FROM I_SalesOrder a
  JOIN I_SalesOrderItem b ON a.SalesOrder = b.SalesOrder
  JOIN I_MaterialText c ON b.Material = c.Material 
       AND c.Language = 'E'
  WHERE a.CreationDate > '20250101'

What is a CDS view?

Core Data Services (CDS) views are a key component of the S/4HANA data model. They provide semantic and analytic capabilities on top of database tables and offer better performance through optimizations like SQL pushdown.

Why should I replace legacy tables with CDS views?

CDS views are optimized for S/4HANA's in-memory architecture and provide additional benefits like built-in analytics capabilities, field value help, and authorization checks.

Can all tables be replaced with CDS views?

Most commonly used tables have CDS view equivalents. For specialized or custom tables, the optimizer will only replace where appropriate and retain original tables otherwise.

Will my query performance always improve?

In most cases, yes. S/4HANA-optimized queries take advantage of the in-memory architecture and database features. However, performance improvements may vary based on the specific query and data volume.