Got remote clients? Use prefetch!

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.