<!DOCTYPE chapter PUBLIC "-//OASIS//DTD DocBook XML V4.5//EN" "http://www.oasis-open.org/docbook/xml/4.5/docbookx.dtd" [
<!ENTITY % CustomDTD SYSTEM "../../../../../../docbook/custom.dtd">
%CustomDTD;
]>
<chapter id="results">
	<title>Results Caching</title>
	<para>Teiid provides the capability to cache the results of
		specific user queries and virtual procedure calls.  This caching technique
		can yield significant performance gains if users of the system submit
		the same queries or execute the same procedures often.</para>
	<section>
		<title>Support Summary</title>
		<itemizedlist>
			<listitem>
				<para>Caching of user query results including XML document model results.</para>
			</listitem>
			<listitem>
				<para>Caching of virtual procedure results.</para>
			</listitem>
			<listitem>
				<para>Scoping of results is automatically determined to be VDB/user (replicated) or session level.</para>
			</listitem>
			<listitem>
				<para>Configurable number of cache entries and time to live.</para>
			</listitem>
			<listitem>
				<para>Administrative clearing.</para>
			</listitem>
		</itemizedlist>
	</section>
	<section>
		<title>User Interaction</title>
		<section>
	   		<title>User Query Cache</title>
	   				<para>User query result set caching will cache result sets based on an exact match
			of the incoming SQL string and PreparedStatement parameter values if present. Caching only applies to SELECT, set query, and stored procedure execution
			statements; it does not apply to SELECT INTO statements, or INSERT,
			UPDATE, or DELETE statements.</para>
			<para>End users or client applications explicitly state whether to use
				result set caching.  This can be done by setting the
				JDBC ResultSetCacheMode execution property to true (default false) or by adding a <link linkend="cache-hint">cache hint</link> to the query.  
				Note that if either of these mechanisms are used, Teiid must also have result set caching enabled (the default is enabled).</para>
	   		<para>The most basic form of the cache hint, <code>/*+ cache */</code>, is sufficient to inform the engine that the results of the non-update command should be cached.</para>  
	   		<example>
	   			<title>PreparedStatement ResultSet Caching</title>
	   			<programlisting>...
PreparedStatement ps = connection.prepareStatement("/*+ cache */ select col from t where col2 = ?");
ps.setInt(1, 5);
ps.execute();
...</programlisting>
				<para>The results will be cached with the default ttl and use the SQL string and the parameter value as part of the cache key.</para>
	   		</example>
	   		<para>The pref_mem and ttl options of the cache hint may also be used for result set cache queries.
	   		If a cache hint is not specified, then the default time to live of the result set caching configuration will be used.
			</para> 
	   		<example>
	   			<title>Advanced ResultSet Caching</title>
	   			<programlisting>/*+ cache(pref_mem ttl:60000) */ select col from t</programlisting>
	   			<para>In this example the memory preference has been enabled and the time to live is set to 60000 milliseconds or 1 minute.  
	   			The ttl for an entry is actually treated as it's maximum age and the entry may be purged sooner if the maximum number of cache entries has been reached.</para>
	   		</example>
			<note><para>Each query is re-checked for authorization using the current user’s permissions, 
			regardless of whether or not the results have been cached.</para></note>
		</section>
   	    <section>
			<title>Procedure Result Cache</title>
			<para>Similar to materialized views, cached virtual procedure results are used automatically when a matching set of parameter values is detected for the same procedure execution.  
			Usage of the cached results may be bypassed with an <code>OPTION NOCACHE</code> clause.  See the <link linkend="nocache">OPTION NOCACHE</link> section for more on its usage.</para>
		</section>
	</section>
	<section>
		<title>Cached Virtual Procedure Definition</title>
		<para>To indicate that a virtual procedure (only definable by Teiid Designer) should be cached, it's definition should include a <link linkend="cache-hint">cache hint</link>.
		<example>
	   		<title>Procedure Caching</title>
	   		<programlisting>/*+ cache */ CREATE VIRTUAL PROCEDURE
BEGIN
	...
END</programlisting>
			<para>Results will be cached with the default ttl.</para>
	   	</example>
	    The pref_mem and ttl options of the cache hint may also be used for procedure caching.
		</para>
		<para>Procedure results cache keys include the input parameter values.  
		To prevent one procedure from filling the cache, at most 256 cache keys may be created per procedure per VDB.</para>
		<para>A cached procedure will always produce all of its results prior to allowing those results to be consumed and placed in the cache.  
		This differs from normal procedure execution which in some situations allows the returned results to be consumed in a streaming manner.</para>
	</section>
	<section>
		<title>Cache Configuration</title>
		<para>By default result set caching is enabled with 1024 maximum entries with a maximum entry age of 2 hours.  
		There are actually 2 caches configured with these settings.  One cache holds results that are specific to sessions and is local to each Teiid instance.
    	The other cache holds VDB scoped results and can be replicated.  
    	See the &jboss-beans; config file or the Console's "Runtime Engine Properties" for tuning the configuration.  
    	The user may also override the default maximum entry age via the <link linkend="cache-hint">cache hint</link>.
    	</para>
		<para>Result set caching is not limited to memory.  There is no explicit limit on the size of the results that can be cached.  
		Cached results are primarily stored in the BufferManager and are subject to it's configuration - including the restriction of maximum buffer space.
		</para>
		<note><para>While the result data is not held in memory, cache keys - including parameter values - may be held in memory.  
		Thus the cache should not be given an unlimited maximum size.</para></note>
		<para>Result set cache entries can be invalidated by data change events.  The max-staleness setting determines how long an entry will remain 
		in the case after one of the tables that contributed to the results has been changed.  See the Developers Guide for further customization.</para>
	</section>
	<section>
		<title>Cache Administration</title>
		<para>The result set cache can be cleared through the AdminAPI using the <code>clearCache</code> method.  
		The expected cache key is "QUERY_SERVICE_RESULT_SET_CACHE".</para>
		<example>
			<title>Clearing the ResultSet Cache in AdminShell</title>
			<programlisting>connectAsAdmin()
clearCache("QUERY_SERVICE_RESULT_SET_CACHE")
...</programlisting>
		</example>
		<para>See the Admin Guide for more on using the AdminAPI and AdminShell.</para>
	</section>
	<section>
		<title>Limitations</title>
		<itemizedlist>
			<listitem>
				<para>XML, BLOB, CLOB, and OBJECT type cannot be used as part of the cache key for prepared statement of procedure cache keys.</para>
			</listitem>
			<listitem>
				<para>The exact SQL string, including the cache hint if present, must match the cached entry for the results to be reused.  
				This allows cache usage to skip parsing and resolving for faster responses.</para>
			</listitem>
			<listitem>
				<para>Result set caching is not transactional.  Transactions depend on (and
					enforce) consistency of data, and cached data is not guaranteed
					to be consistent with the data store’s data.</para>
			</listitem>
			<listitem>
				<para>Clearing the results cache clears all cache entries for all VDBs.</para>
			</listitem>
		</itemizedlist>
	</section>
</chapter>