|Bookshelf Home | Contents | Index | PDF|
After tuning the long-running SQL statements, further tests can be run to determine the optimal batch size for each entity to be processed. The correct batch size varies and is influenced by the amount of buffer cache available. Optimal batch ranges have been observed to range anywhere between 500 and 15,000 rows. You should run several tests with different batch sizes to determine the size that provides the best rate of EIM transactions per second. Using the setting Trace Flag = 1 while running EIM helps in this task because you are then able to see how long each step takes and how many rows were processed by the EIM process.
Furthermore, for Microsoft SQL Server and Oracle environments, you should limit the number of records in the EIM tables to those that are being processed. For example, if you have determined that the optimal batch size for your implementation is 19,000 rows per batch and you are going to be running eight parallel EIM processes, then you should have 152,000 rows in the EIM table. Under no circumstances should you have more than 250,000 rows in any single EIM table because this reduces performance.
The restrictions mentioned in the example above do not apply to DB2 environments. As long as an index is being used to access the EIM tables, the numbers of rows in the EIM tables does not matter in DB2 environments.
NOTE: The number of rows you can load in a single batch may vary depending on your physical machine setup and on which table is being loaded. To reduce demands on resources and improve performance, you should generally try to vary batch sizes to determine the optimal size for each entity to be processed. In some cases, a smaller batch size can improve performance. But for simpler tables such as S_ASSET, you may find that loads perform better at higher batch sizes than for more complex tables such as S_CONTACT.
|Performance Tuning Guide|