CACHE Statements

The syntax for the CACHE statement is:

  CACHE [CACHE_TABLE_NAME] [ WITH TRUNCATE | AUTOCOMMIT | SCHEMA ONLY | DROP EXISTING | ALTER SCHEMA ] SELECT STATEMENT

CACHE statements provide complete control over the data that is cached and the table to which it is cached. The cache statement executes the SELECT statement and caches its results to a similarly named table in the cache database or to the CACHE_TABLE_NAME, if it is specified. The provider updates or inserts rows to the cache depending on whether or not they already exist in the cache, so the primary key, which is used to identify existing rows, must be included in the selected columns. The cache statement may include options that alter its behavior; currently the following options are supported:

WITH TRUNCATE

If this option is set, the provider removes existing rows in the cache table before adding the selected rows. Use this option if you want to refresh the entire cache table but keep its existing schema.

AUTOCOMMIT

If this option is set, the provider will commit each row individually. Use this option if you want to ignore the rows that could not be cached due to some reason. By default, the entire result set is cached as a single transaction.

DROP EXISTING

If this option is set, the provider drops the existing cache table before caching the new results. Use this option if you want to refresh the entire cache table, including its schema.

SCHEMA ONLY

If this option is set, the provider creates the cache table based on the SELECT statement without executing the query.

ALTER SCHEMA

If this option is set, the provider alters the schema of the existing table in the cache if it does not match the schema of the SELECT statement. This option will result in new columns or dropped columns, if the schema of the SELECT statement does not match the cached table.

Common Queries

Use the following cache statement to cache all rows of a table.

CACHE SELECT * from Account;

Use the following cache statement to cache all rows of a table into the cache table CachedAccount.

CACHE CachedAccount SELECT * from Account;

Use the following cache statement for incremental caching. The DateModified column may not exist in all tables. The statement below just shows how incremental caching would work if there were such a column. Also, notice that, in this case, we specifically omitted the WITH TRUNCATE and DROP EXISTING options, which would have deleted all existing rows.

CACHE CachedAccount SELECT * from Account WHERE DateModified > '2013-04-04';

Use the following cache statements for creating a table with all available columns and then caching only a few of them. The sequence of statements below cache only InternalId and AcctName even though the cache table CachedAccount has all the columns in Account.

CACHE CachedAccount SCHEMA ONLY SELECT * from Account;
CACHE CachedAccount SELECT InternalId, AcctName from Account;

Refer to the Caching Data section for code snippets.