XDBRIDPOOL is an optional environment variable that is specified in the [Server] topic of the xdb.ini file to enhance performance of the Query Optimizer.
The Query Optimizer may at times choose to use an algorithm called list prefetch when analyzing data access paths. This algorithm entails retrieving Record IDs (RIDs) from an index file and then sorting them. List prefetch is invoked when multiple index access is required or when data needs to be retrieved in physical order.
The format for this environment variable is:
XDBRIDPOOL=number-of-kilobytes
where number-of-kilobytes is the amount of memory to allocate for each statement's RID pool. The maximum size that can be specified is dependent on the amount of system memory that is available.
The RID Pool is the buffer that the server uses to store RIDs of this type. The default RID pool size is 512K per query. Since each RID contains 4 bytes, this default RID pool size can hold a maximum of 131,072 RIDs. If the RID pool size is exceeded, the list prefetch algorithm may be aborted, severely hampering performance.
The number of RIDs that can possibly enter the RID pool is determined by the selection criteria defined by a particular WHERE clause. A good rule of thumb for estimating an appropriate RID Pool Size is to determine the number of records that exist in the largest table, and then use the following formula:
RID Pool Size = (max records) * (.20) * (4 bytes per RID)
For example, a table containing 3,000,000 records would have an optimal RID Pool Size determined as shown below:
3,000,000 * .20 * 4 = 2,400,000 bytes
Converting this to kilobytes, we get:
2,400,000 / 1024 = 2344K
This RID Pool Size would allow an index condition that qualifies up to 20% of the table's rows to fit into the RID Pool.
The following XDBRIDPOOL environment variable setting would make available a 2400 kilobyte RID Pool per statement.
XDBRIDPOOL=2400