What is prefetch? It is a group of database startup parameters that when use correctly and together can optimize the transfer of data from the database to your remote clients.
Progress introduced parameters to improve client/server performance that are collectively referred to as -prefetch*. These parameters allow much larger amounts of data to be transferred in certain network messages (namely so-called “no-lock queries”), which can, in many cases, significantly improve performance and reduce network “chattiness”.
Observations
- Prefetch delay: eliminates a network message that is used to send the initial record in a query before the rest of the result set
- Prefetch Number of Records: specifies the maximum number of records that can be sent in a "query" message. Larger values are much more efficient
- Prefetch Factor: establishes a percentage of a network message required to contain prefetched data before sending the message to a waiting remote client
- Prefetch Priority: Uses a "pollskip" that adds n records to the network message of an in-process prefetch query without additional polling.
Corrective action
Add the following parameters to your database startup parameter file and restart the database:
-prefetchDelay
-prefetchFactor 100
-prefetchNumRecs 10000
-preFetchPriority 1000
AND ... maximize the buffers transporting the data across the network. See maxMsg < 8192.