Caché MultiValue Query Language (CMQL) Reference
PREFETCH clause
[Home] [Back] [Next]
InterSystems: The power behind what matters   
Class Reference   
Search:    

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:
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.