Improving Performance for Index Builds

Whenever possible, the database server uses parallel processing to improve the response time of index builds. The number of parallel processes is based on the number of fragments in the index and the value of the PSORT_NPROCS environment variable. The database server builds the index with parallel processing even when the value of PDQ priority is 0.

You can often improve the performance of an index build by taking the following steps:

1. Set PDQ priority to a value greater than 0 to obtain more memory than the default 128 kilobytes.

   When you set PDQ priority to greater than 0, the index build can take advantage of the additional memory for parallel processing.

   To set PDQ priority, use either the PDQPRIORITY environment variable or the SET PDQPRIORITY statement in SQL.

2. Do not set the PSORT_NPROCS environment variable.
3. It is recommended that you not set the PSORT_NPROCS environment variable. If you have a computer with multiple CPUs, the database server uses two threads per sort when it sorts index keys and PSORT_NPROCS is not set. The number of sorts depends on the number of fragments in the index, the number of keys, the key size, and the values of the PDQ memory configuration parameters.
4. Allocate enough memory and temporary space to build the entire index.
   1. Estimate the amount of virtual shared memory that the database server might need for sorting.

      For more information, see Estimating Memory Needed for Sorting.

   2. Specify more memory with the DS_TOTAL_MEMORY and DS_MAX_QUERIES configuration parameters.
   3. If not enough memory is available, estimate the amount of temporary space needed for an entire index build.

      For more information, see Estimating Temporary Space for Index Builds.

   4. Use the onspaces -t utility to create large temporary dbspaces and specify them in the DBSPACETEMP configuration parameter or the DBSPACETEMP environment variable.

      For information on how to optimize temporary dbspaces, see Configuring Dbspaces for Temporary Tables and Sort Files.