PREFETCH clause

Improves query performance.

Synopsis

PREFETCH

Description

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.

To activate disk I/O prefetch, you must do the following:

Add the PREFETCH keyword anywhere within your CMQL statement. For example:
```
SELECT ACCOUNTS WITH CUSTOMER LIKE ...COOPER... PREFETCH
```
Start one or more prefetch demons. The following ObjectScript example starts three prefetch demons:
```
Start(njobs) 
    FOR i=1:1:njobs {JOB PreFetch}
    QUIT
PreFetch ; 
    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.

USER:TIME COUNT Bigfile
   627706 Items counted.
 [256] Execution time 126.915667 Seconds.

USER:TIME COUNT Bigfile PREFETCH
   627706 Items counted.
 [256] Execution time 20.412388 Seconds.

Queries vary in their prefetch performance improvement.