To speed up index rebuild operations, do the following:
Answer yes when prompted whether you have enough disk space for sorting.
Increase the Speed Sort (-TB) startup parameter to 24K. (If you are very short of memory, use 16K or 8K.) This improves sort performance; however, it also uses more memory and disk space. The default is 8, and can be set as high as 64.
Increase the Merge Number (-TM) parameter to 32 (unless memory is scarce).
Use the Sort Grouping (-SG) parameter. A large -SG value requires more memory allocation and more file handles. To determine the amount of memory (in kilobytes) needed for each index group, add 1 to the merge number (the value of -TM) and multiply the sum by the speed sort block size (the value of -TB). Memory consumption for each index group equals (-TM + 1) * -TB.
Change the Temporary Directory (-T) startup parameter to store the temporary files on another disk.
Utilize threading parameters.
Specify -datascanthreads to specify the number of concurrent threads spawned to scan table data associated with the index being rebuilt.
Specify -mergethreads to specify the number of concurrent threads spawned to execute the sort and merge phases of index rebuild.
The database engine uses the following algorithm to rebuild indexes for each record:
1. Read the index key fields and store in the first available SRT file block.
2. Allocate additional SRT file blocks of the specified block size as required to hold all index keys.
3. Sort the keys in each block, then merge the keys to produce a sorted file.
A similar technique is used to sort records when there is no index to satisfy a BY clause.
A larger block size can improve index rebuild performance considerably. A larger block size means less SRT block allocation overhead and fewer quicksort operations on individual blocks.
You might have to run the application several times using different block size values to determine the optimal value. If you experience extreme memory shortages when running an OpenEdge session, try setting the block size to 1 to reduce memory consumption.
During index rebuild, try setting -TB to 31, if memory and disk space are available. If the index rebuild fails, try successively smaller values. Remember, a larger value for -TB improves sort performance but uses more memory. The -TB setting has a significant impact on the size of the SRT temporary file. The SRT file size depends on the number of session compile files, and the number and size of sort operations.
Memory usage depends on the number of sorts simultaneously occurring. The simultaneous sorts are logically equivalent to nested FOR EACH statements. You can estimate memory usage as follows, where M is the estimated memory usage:
M = sort-block-size *
(number-of-simultaneous-sorts+Merge-Number(-TM) parameter)
Index rebuild always requires eight simultaneous sorts, so during index rebuild:
Answer yes when prompted whether you have enough disk space for sorting.