Performance Tuning Guide > Tuning Siebel EIM for Performance > Troubleshooting EIM Performance >

Optimizing SQL for EIM

During this process, you need to be able to run several similar batches. If you do not have enough data with which to experiment, you may need to back up and restore the database between runs, so that you can continue processing the same batch.

First, you should run an EIM job with the following flag settings: Error flag = 1, SQL flag = 8, and Trace flag = 3. This will produce a log file that contains SQL statements and shows how long each statement took. Identify SQL statements that are taking too long (on a run of 5000 rows in a batch, look for statements that took longer than one minute). These are the statements that you want to concentrate on, and you should consult an experienced database administrator at this point. The process of optimizing the SQL for EIM involves the following:

• Use the respective database vendor's utility or a third-party utility to analyze the long-running SQL statements.
• Based on the review of the data access paths, review the SQL statements for proper index usage. There may be cases where an index is not used at all or the most efficient index is not being chosen. This may require a thorough analysis.
• Based on this analysis, use a systematic approach to tuning these long-running statements. You should perform one change at a time and then measure the results of the change by comparing them to the initial benchmarks. For example, you may find that dropping a particular index to improve the performance of one long-running statement might negatively impact the performance of other SQL statements.

  The decision on whether to drop the index should be based on the impact to the overall process as opposed to the individual long-running SQL statement. For this reason, it is important that one change be implemented at a time in order to measure the impact of the change.

• After repetitively going through and optimizing each long-running SQL statement, the focus can be shifted to other tuning measures, such as increasing the number of records processed in the EIM table at a time and the running of parallel EIM tasks.