Improving performance for index builds
You can improve performance for index builds by adjusting the PDQ priority and by allocating enough memory and temporary space for the entire index.
About this task
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:
Procedure
- 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.
- Do 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.
- Allocate enough memory and temporary space to build the
entire index.