Each item descriptor in a SQL repository has its own separate item cache and query cache. This lets you size and flush caches separately for separate item types. The item cache holds property values for repository items. It is indexed by the repository item IDs. The query cache holds the repository IDs of items that match particular queries in the cache.
For example, suppose you have a query like this:
color = red AND size = XXL
If query caching is enabled, the first time this query is issued, the result set is retrieved from the database and stored in the query cache. Then, the next time this same query is issued, the SQL repository can retrieve the result set from the cache, rather than needing to access the database.
The item caches hold the values of repository items. Repository queries are performed in two passes, using two separate SELECT statements. The first statement gathers the IDs of the repository items that match that query. The SQL repository then examines the result set from the first SELECT statement and finds any items that already exist in the item cache. A second SELECT statement retrieves from the database any items that are not in the item cache.
Query caching is turned off by default. If items in your repository are updated frequently, or if repeated queries are not common, you may not receive enough benefit from a query cache to justify the minor amount of overhead in maintaining the cache. See Cache Configuration for more information about enabling the query cache and setting the sizes of the item and query caches.
