The PREFETCH keyword can be used to improve performance of CMQL queries by using Caché prefetch demons to read in data from disk prior to the data being used by the query.

PREFETCH is an optional keyword that can be specified anywhere within a CMQL query.

Performance gains vary with different queries. In some cases the gains may be negligible. Other cases may get a five-fold improvement or more. Of course, if the data already exists in shared memory (used as a data cache) there is no disk I/O, and therefore no prefetch performance improvement.

Note that in all performance tuning exercises there could be a penalty to pay. For example, if by using PREFETCH your disks get heavier usage, this may have (or may not have) an adverse effect on concurrent interactive users.

Start one or more prefetch demons. The following ObjectScript example starts three prefetch demons:

Start(njobs)FORi=1:1:njobs{JOBPreFetch}QUITPreFetch; DO$ZU(180,1)QUIT

From the Terminal prompt, issue:

%SYS>DO Start^ROUTINE(3)

Where ROUTINE is the name of the ObjectScript routine. This command starts up three background jobs starting at the PreFetch label. The call to $ZU(180,1) means the background job becomes a prefetch background demon.

Examples

This is an example of a simple CMQL statement run with and without PREFETCH. There is around 48 Mb of data on an installation using 64 Mb of shared memory.