In data integration, creating a functional job is only the first step. To ensure a solution is production-ready, you must optimize it for maximum efficiency.
Beyond SQL push-down, SAP Data Services provides several built-in techniques to enhance job performance. Applying these methods correctly improves processing speed, resource management, and overall throughput.
This lesson covers four key optimization techniques:
- Data Caching
- Join Ranking
- Bulk Loading
- Process Slicing
Data Caching
Even when SQL push-down is unavailable, you can improve the performance of data transformations by caching as much data as possible. Caching reduces the frequency of database access by storing data in memory, provided the dataset fits within the available system memory.
The following table describes operations that benefit from in-memory data caching during data flow processing.
Operations Benefiting from Data Caching
| Operation | Description |
|---|---|
| Joins | Data Services reads the inner source of a join for every row in the outer source. Caching the inner source significantly improves processing speed. |
| Table comparisons | Data Services reads the comparison table for every row in the source. Cache the comparison table to reduce database overhead. |
| Lookups | If a lookup table is located in a remote database, cache it in memory to minimize access time. |
Caching Joins
Joins are a primary example of how caching can enhance performance.
Watch this video to learn more about caching joins.
| Resources |
|---|
For detailed information on join caching, refer to the Performance Optimization Guide. |
For more information on persistent cache datastores, refer to the Designer Guide. |
Join Ranking
You can also optimize Query transform join operations by assigning a join rank to each source in the join.
When you assign join ranks:
- SAP Data Services evaluates the rank relative to other tables and files within the data flow.
- The optimizer—the internal engine component—processes sources with higher rank values before those with lower values.
- The execution order is determined by the join rank and, for left outer joins, the order specified in the FROM clause.
Combining Joins
The optimizer manages data joins in the following ways:
- It can merge joins from consecutive Query transforms into a single transform and reassign join ranks accordingly.
- It considers upstream join rank settings when executing joins.
| Resources |
|---|
For examples, refer to the Performance Optimization Guide. |
Bulk Loading
Use bulk loading to transfer large volumes of data efficiently. Instead of inserting data row-by-row, bulk loading utilizes optimized methods specific to the target database architecture.
SAP Data Services supports bulk loading for most databases, allowing you to load—and sometimes read—data in bulk rather than using standard SQL statements.
Bulk Loading Considerations
Keep the following points in mind when using bulk loading and reading:
- Configure bulk-loading options in the target table editor under the Options and Bulk Loader Options tabs.
- Most databases do not support bulk loading when using template tables.
- Supported operation codes vary by database. While most support NORMAL and INSERT, only specific databases support UPDATE and DELETE during bulk operations.
| Resources |
|---|
For more information on bulk loading options, refer to the Performance Optimization Guide. |
Process Slicing
Process slicing optimizes jobs by dividing a single data flow into multiple sub-data flows.
Sub-Data Flows
- They process smaller datasets or fewer transforms, which reduces the virtual memory consumption of each individual process.
- They maximize physical memory usage, as each sub-data flow can access up to 2 GB of memory.
Process slicing is configured in the Advanced tab of the Query transform. You can run memory-intensive operations as separate processes, as shown in the following figure:
Summary
In this lesson, you have learned about advanced performance techniques.
- Data Caching reduces repeated database reads by storing frequently accessed data, like join inner sources, in memory.
- Join Ranking controls the order in which sources are processed, helping the optimizer execute joins more efficiently.
- Bulk Loading transfers large data volumes faster by bypassing row-by-row SQL inserts and using database-optimized methods.
- Process Slicing splits a data flow into smaller sub-flows, reducing memory consumption and improving overall throughput.