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.
- Estimate the amount of virtual shared memory that the
database server might need for sorting.
- Specify more memory with the DS_TOTAL_MEMORY and DS_MAX_QUERIES
configuration parameters.
- If not enough memory is available, estimate the amount
of temporary space needed for an entire index build.
- Use the onspaces -t utility to create large temporary
dbspaces and specify them in the DBSPACETEMP configuration parameter
or the DBSPACETEMP environment variable.