Understanding SQL Push-Down Operations in SAP Data Services

Objective

After completing this lesson, you will be able to understand SQL Push-Down Operations in SAP Data Services.

Push Down Operations

SAP Data Services can optimize performance by leveraging the processing power of the database engine.

This mechanism, known as SQL push-down, delegates parts of the transformation process to the database by generating SQL statements.

Because databases often process data more efficiently, this internal processing significantly reduces time-consuming data transfers between the database server and SAP Data Services.

Watch the following video to learn more about SQL push-down.

Resources
For a comprehensive list of operators, functions, and transforms supported for push-down in SAP Data Services, refer to SAP Note 2212730: SAP Data Services push-down operators, functions, and transforms.

The View Optimized SQL Feature

Before running a job, you can view the SQL generated by the data flow. This allows you to adjust your design to maximize push-down operations, which improves performance and optimizes the data flow.

SAP Data Services only displays SQL generated for table sources. It does not show SQL for other sources or functions, such as the lookup function, the Key Generation transform, the key_generation function, the Table Comparison transform, or target tables.

The following figure illustrates the Optimized SQL dialog box.

The image displays a window titled Optimized SQL showing a SQL query for a dataset named DEMO_Source, with the query selecting multiple columns from a table named ODS_CUSTOMER and applying the function fn ucase to the column ODS_CUSTOMER_NAME1, along with options to Find, Save As, and Close at the bottom.

Summary

In this lesson, you have learned about the SQL push-down feature of SAP Data Services.

SQL push-down delegates data transformation processing to the database, reducing data transfer and improving performance.
Full push-down sends all operations to the database; partial push-down delegates only the SELECT statement.
Full push-down requires all operations to qualify and source and target to share a datastore or database link.
Aggregations, filters, joins, and projections are eligible for push-down; unsupported functions block it.
Use the View Optimized SQL feature to preview generated SQL and maximize push-down before running a job.

Next lesson