SAP Data Services System & Performance Optimization

[Former Member]

The following post is an attempt to summarize the Data Services Performance Optimization Guidelines and make them shorter and easier to read. In order to achieve an improvement I suggest you to stick to the following sequence: ‘measure’, ‘act’, ‘measure’.

Before moving to actual DS optimization it is recommended to first test your environment.

1.     Enhance the system capabilities

     - Source OS and database server

• Increase the value of the read-ahead protocol offered by Windows
• Expedite the SELECT statement of the DB through indexing, caching and increase of the database server’s I/O size

     - Target OS and database server

• Turn on the asynchronous I/O
• Expedite the INSERT and UPDATE of your target database

     - The network

• Set network buffers to reduce the number of round trips to the database servers across the network

2.      Measure job performance

     - Check system utilization

• CPU utilization
• Memory utilization

     - Read log files

     - Read Monitor Logs

     - Read Performance Monitor

     - Read Operational Dashboard

To compare previous execution times you can view the Operational Dashboard or the Job Execution History found in the Administrator section, both located in the Management Console.

3.     Job execution strategies

     - Push down operations- applicable for database sources and targets

          For SQL sources and targets, SAP Data Services creates database-specific SQL statements based on the data flow diagrams in a job. The software           generates SQL SELECT statements to retrieve the data from source databases. To optimize performance, the software pushes down as many SELECT           operations as possible to the source database and combines as many operations as possible into one request to the database. The operations within the           SELECT statement that the software can push down to the database are: Aggregations, Distinct rows, Filtering, Joins, Ordering, Projection, Functions.

     - Improving throughput

• Use caching as much as possible
• Bulk load to the target
• Minimize extracted data
• Change array fetch size
• Increase the rows per commit
• Increase the Degree of parallelism