3732192 - Transformation Flow loading from SQL View uses excessive memory compared to Graphical View - SAP Datasphere

• Transformation Flow loaded from a SQL view consumes much more memory than expected.
• When switching the source to a graphical view, memory usage is very low even when more records are written to the target.

• Product: SAP Datasphere
• Transformation Flows

1. Run a Transformation Flow that sources data from a SQL view and monitor peak memory usage; 
2. Run an equivalent Transformation Flow that sources data from a graphical view and monitor peak memory usage.

• The high memory usage with the SQL view is caused by materialization in case the SQL includes a complex window function (for example, ROW_NUMBER) and uses SELECT *, which prevents streaming. The engine must load and sort the full intermediate dataset in memory before filtering to final records.
• In contrast, graphical views enable pushdown and streaming, allowing the engine to retrieve only the necessary columns and rows in smaller chunks.

Prefer graphical views for complex logic so that the optimizer can push down operations and stream data efficiently.

If a SQL view is required, consider the following optimizations:

• Replace SELECT * with an explicit column list to reduce data width.
• Push filters (for example, date ranges and other predicates) directly inside the initial query blocks (CTEs) to reduce the volume of data being sorted.

datasphere, transformation flow, sql view, graphical view, high memory, peak memory, materialization, window function, row_number, select *, pushdown, streaming, memory usage, optimization, trf , KBA , DS-DI-TRF , Transformation Flows , How To