Thursday, January 9, 2014

How can I tune my EIM batches to improve performance?


You should try the following options to improve EIM performance:

1.    Verify that all indices exist for the tables involved.

2.    Limit tables and columns to be processed using ONLY BASE TABLES/COLUMNS configuration parameters to minimize EIM processing.

3.    Consider switching off transaction logging during the EIM run. This will definitely improve performance. However, it must be balanced with the need for mobile users to re-extract afterward.

4.    Try using different batch sizes. Large batch sizes are often not efficient.

NOTE: Although the limit of rows you can process in a batch depends on your physical machine setup, you should not use a batch size of more than 100,000 rows.

For import processes, and delete processes that use the DELETE EXACT parameter, it is recommended that you use around 20,000 rows in a single batch.

Try using batch ranges (BATCH = x–y). This allows you to run with smaller batch sizes and avoid the startup overhead on each batch. The maximum number of batches that you can run in an EIM process is 1,000.

5.  Perform regular table maintenance on interface tables. Frequent insert or delete operations on interface tables can cause fragmentation in the tables. Consult your database administrator to detect and correct fragmentation in the interface tables.

6.    Delete batches from interface tables on completion. Leaving old batches in the interface table wastes space and could adversely affect performance.

7.    Run independent EIM jobs in parallel. EIM jobs that have no interface or base tables in common can run in parallel.

8.    Set the USING SYNONYMS parameter to FALSE in the .ifb configuration file to indicate that account synonyms do not need to be checked.

9.    Avoid using the UPDATE PRIMARY KEYS parameter in the .ifb configuration file.

10.  If all else fails, set Trace Flags=1 and SQL Trace Flags=8 and rerun the batch. The resulting task log will allow you to identify slow-running steps and queries.