When processing database data, SAP Data Services can leverage the power of the database engine. That may be very important for performance reasons. The mechanism applied is called SQL pushdown: part of the transformation is "pushed down" to the database in the form of generated SQL statements. Databases are often able to process data much faster. Internal processing within the database layer avoids or significantly reduces time-consuming data transfers between database server memory and Data Services memory and vice versa.
Data Services examines the database and its environment when determining which operations to push down to the database. There are two types of push-down operations:
- Full: Pushes down all transform operations to the database servers and the data streams directly from the source database to the target database.
- Partial: Pushes down the SELECT statements to the source database server.
Full Push-Down Operations
The Data Services optimizer always tries to do a full push–down operation. Full push–down operations can be pushed down to the databases and to the data streams directly from the source database to the target database.
For example, Data Services sends SQL INSERT INTO... SELECT statements to the target database and it sends SELECT to retrieve data from the source.
Full Push-Down Conditions
Full push–down operations to the source and target databases are only possible when the following conditions are met:
- All of the operations between the source table and target table can be pushed down.
- The source and target tables are from the same datastore or they are in datastores that have a database link defined between them.
Partial Push-Down Operations
When a full push–down operation is not possible, Data Services tries to push down a partial push-down with a SELECT statement to the source database.
SELECT Statement Operations that can be Pushed Down
The table lists operations within the SELECT statement that can be pushed to the database:
| Operation | Description |
|---|---|
| Aggregations | Used with a GROUP BY statement to produce a data set smaller than or the same size as the original data set. |
| Distinct rows | Data Services only outputs unique rows when you use distinct rows. |
| Filtering | Produce a data set smaller than or equal to the original data set. |
| Joins | Produce a data set smaller than or similar in size to the original tables. |
| Ordering | Ordering does not affect data set size. Data Services can efficiently sort data sets that fit in memory. Since Data Services does not perform paging by writing out intermediate results to disk, use the DBMS itself to order large data sets. |
| Projections | Produce a smaller data set because they only return columns referenced by a data flow. |
| Functions | Most Data Services functions that have equivalents in the underlaying database are appropriately translated. |
Operations that Can't be Pushed Down
Data Services cannot push some transform operations to the database, for example:
- Expressions that include Data Services functions without database correspondents.
- Load operations that contain triggers.
- Transforms other than the Query transform.
- Joins between sources that are on different database servers without database links defined between them.
Note
For a full list of operators, functions and transforms that you can use as push-down functions with SAP Data Services, see SAP Note 2212730, SAP Data Services push-down operators, functions, and transforms.
