Author: shawkins Date: 2010-08-26 11:29:47 -0400 (Thu, 26 Aug 2010) New Revision: 2492 Added: branches/7.1.x/documentation/caching-guide/src/main/docbook/en-US/content/hint-option.xml branches/7.1.x/documentation/caching-guide/src/main/docbook/en-US/content/results.xml Removed: branches/7.1.x/documentation/caching-guide/src/main/docbook/en-US/content/cachehint.xml branches/7.1.x/documentation/caching-guide/src/main/docbook/en-US/content/resultset.xml Modified: branches/7.1.x/build/kits/jboss-container/teiid-releasenotes.html branches/7.1.x/cache-jbosscache/src/main/java/org/teiid/cache/jboss/ClusterableCacheFactory.java branches/7.1.x/documentation/caching-guide/src/main/docbook/en-US/content/matviews.xml branches/7.1.x/documentation/caching-guide/src/main/docbook/en-US/main.xml branches/7.1.x/documentation/reference/src/main/docbook/en-US/content/sql_support.xml branches/7.1.x/engine/src/main/java/org/teiid/cache/DefaultCache.java branches/7.1.x/engine/src/main/java/org/teiid/cache/DefaultCacheFactory.java branches/7.1.x/engine/src/main/java/org/teiid/dqp/internal/process/CachedResults.java branches/7.1.x/engine/src/main/java/org/teiid/dqp/internal/process/DQPCore.java branches/7.1.x/engine/src/main/java/org/teiid/dqp/internal/process/Request.java branches/7.1.x/engine/src/main/java/org/teiid/dqp/internal/process/SessionAwareCache.java branches/7.1.x/engine/src/main/java/org/teiid/query/optimizer/relational/RelationalPlanner.java branches/7.1.x/engine/src/main/java/org/teiid/query/tempdata/TempTableDataManager.java branches/7.1.x/engine/src/main/java/org/teiid/query/util/CommandContext.java branches/7.1.x/engine/src/main/resources/org/teiid/query/i18n.properties branches/7.1.x/engine/src/test/java/org/teiid/query/processor/TestMaterialization.java branches/7.1.x/engine/src/test/java/org/teiid/query/processor/TestProcessor.java branches/7.1.x/engine/src/test/java/org/teiid/query/unittest/RealMetadataFactory.java Log: TEIID-1213 adding procedure result caching Modified: branches/7.1.x/build/kits/jboss-container/teiid-releasenotes.html =================================================================== --- branches/7.1.x/build/kits/jboss-container/teiid-releasenotes.html 2010-08-25 17:56:09 UTC (rev 2491) +++ branches/7.1.x/build/kits/jboss-container/teiid-releasenotes.html 2010-08-26 15:29:47 UTC (rev 2492) @@ -26,6 +26,7 @@ </UL> <H2><A NAME="Highlights"></A>Highlights</H2> <UL> + <LI><B>Procedure Result Caching</B> - virtual procedure definitions may use a cache hint to cache results in the result set cache. <LI><B>Improved Plan Caching</B> - plans used by internal materialization and stored procedure plans will be automatically cached in the prepared plan cache. Improvements were also made to reduce the memory footprint of the plans. <LI><B>Refined Load Balancing and Fail Over</B> - clients can use use the statement "SET NEWINSTANCE TRUE" to allow their connection to select a new server instance. See the Client Developer's Guide for more information. </UL> Modified: branches/7.1.x/cache-jbosscache/src/main/java/org/teiid/cache/jboss/ClusterableCacheFactory.java =================================================================== --- branches/7.1.x/cache-jbosscache/src/main/java/org/teiid/cache/jboss/ClusterableCacheFactory.java 2010-08-25 17:56:09 UTC (rev 2491) +++ branches/7.1.x/cache-jbosscache/src/main/java/org/teiid/cache/jboss/ClusterableCacheFactory.java 2010-08-26 15:29:47 UTC (rev 2492) @@ -45,7 +45,7 @@ if (this.delegate == null) { Object cacheManager = getClusteredCache(); if (cacheManager == null) { - this.delegate = new DefaultCacheFactory(); + this.delegate = new DefaultCacheFactory(config); } else { try { Deleted: branches/7.1.x/documentation/caching-guide/src/main/docbook/en-US/content/cachehint.xml =================================================================== --- branches/7.1.x/documentation/caching-guide/src/main/docbook/en-US/content/cachehint.xml 2010-08-25 17:56:09 UTC (rev 2491) +++ branches/7.1.x/documentation/caching-guide/src/main/docbook/en-US/content/cachehint.xml 2010-08-26 15:29:47 UTC (rev 2492) @@ -1,88 +0,0 @@ -<!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="cache-hint"> - <title>Cache Hint</title> - <para>A cache hint can be used to:</para> - <itemizedlist> - <listitem><para>Indicate that a user query is eligible for result set caching. - </para></listitem> - <listitem><para>Set the result set query cache entry memory preference or time to live. - </para></listitem> - <listitem><para>Set the materialized view memory preference, time to live, or updatablity. - </para></listitem> - </itemizedlist> - <para><synopsis>/*+ cache[([pref_mem] [ttl:n] [updatable])] */</synopsis> - <itemizedlist> - <listitem><para><emphasis>pref_mem</emphasis> - if present indicates that the cached results should prefer to remain in memory. They are not however required to be memory only. - </para></listitem> - <listitem><para><emphasis>ttl:n</emphasis> - if present n indicates the time to live value in milliseconds. - </para></listitem> - <listitem><para><emphasis>updatable</emphasis> - if present indicates that the cached results can be updated. - </para></listitem> - </itemizedlist> - </para> - <section> - <title>ResultSet Cache Query</title> - <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>While no options are specified with the cache hint, it still informs the engine to use ResultSet caching.</para> - </example> - <para>The pref_mem and ttl options may also be used for ResultSet cache queries, however updatable only has an effect on materialized view tables.</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> - <para>See the <link linkend="resultset">ResultSet Caching Chapter</link> for more.</para> - </section> - <section> - <title>Materialized Views</title> - <para>The cache hint, when used in the context of an internal materialized view transformation query, provides the ability to fine tune the materializated table. - The hint is not used for materialization targeted at an external source. See the <link linkend="resultset">Materialized View Chapter</link> for more on materialized views.</para> - <para>The pref_mem option also applies to internal materialized views. Internal table index pages already have a memory preference, so the perf_mem option indicates that the data pages should prefer memory as well.</para> - <section> - <title>TTL Snapshot Refresh</title> - <para>When the ttl is specified in the cache hint, a full refresh of the materialized view will be triggered automatically after the specified time interval. - The refresh is equivalent to <code>CALL SYS.refreshMatView('view name', false)</code>, but performed asynchronously so that user queries do not block on the load. - </para> - <section> - <title>Limitations</title> - <itemizedlist> - <listitem><para>The automatic ttl refresh is not intended for complex loading scenarios, as nested materialized views will be used by the refresh query. - </para></listitem> - <listitem><para>The automatic ttl refresh is performed lazily, that is it is only trigger by using the table after the ttl has expired. - For infrequently used tables with long load times, this means that data may be used well past the intended ttl. - </para></listitem> - </itemizedlist> - </section> - </section> - <section> - <title>Updatable</title> - <para>When the updatable option is specified, the materialized view may be targeted by the system function <code>refreshMatViewRow</code>. - The refreshMatViewRow function updates a single row of an internal materialized with the supplied key value. - The refresh query does use nested caches, so this refresh method should be used with caution. - </para> - <para>When the updatable option is not specified, accessing the materialized view table is more efficient because modifications do not need to be considered. - Therefore, only specify the updatable option if row based incremental updates are needed. Even when performing row updates, full snapshot refreshes may be needed to ensure consistency.</para> - </section> - </section> - <section> - <title>Limitations</title> - <itemizedlist> - <listitem><para>The form of the query hint must be matched exactly for the hint to have affect. - For a user query if the hint is not specified correctly, e.g. /*+ cach(pref_mem) */, it will not be used by the engine nor will - there be an informational log. As a workaround, the query plan may be checked though (see the Client Developers Guide) to see if the user command - in the plan has retained the proper hint. - </para></listitem> - </itemizedlist> - </section> -</chapter> \ No newline at end of file Copied: branches/7.1.x/documentation/caching-guide/src/main/docbook/en-US/content/hint-option.xml (from rev 2478, branches/7.1.x/documentation/caching-guide/src/main/docbook/en-US/content/cachehint.xml) =================================================================== --- branches/7.1.x/documentation/caching-guide/src/main/docbook/en-US/content/hint-option.xml (rev 0) +++ branches/7.1.x/documentation/caching-guide/src/main/docbook/en-US/content/hint-option.xml 2010-08-26 15:29:47 UTC (rev 2492) @@ -0,0 +1,60 @@ +<!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="cache-hint-options"> + <title>Hints and Options</title> + <section id="cache-hint"> + <title>Cache Hint</title> + <para>A query cache hint can be used to:</para> + <itemizedlist> + <listitem><para>Indicate that a user query is eligible for result set caching and set the cache entry memory preference or time to live. + </para></listitem> + <listitem><para>Set the materialized view memory preference, time to live, or updatablity. + </para></listitem> + <listitem><para>Indicate that a virtual procedure should be cachable and set the cache entry memory preference or time to live. + </para></listitem> + </itemizedlist> + <para><synopsis>/*+ cache[([pref_mem] [ttl:n] [updatable])] */ sql ...</synopsis> + <itemizedlist> + <listitem><para>The cache hint should appear at the beginning of the SQL. It will not have any affect on INSERT/UPDATE/DELETE statements or virtual update procedure definitions.</para></listitem> + <listitem><para><emphasis>pref_mem</emphasis> - if present indicates that the cached results should prefer to remain in memory. They are not however required to be memory only. + </para></listitem> + <listitem><para><emphasis>ttl:n</emphasis> - if present n indicates the time to live value in milliseconds. + </para></listitem> + <listitem><para><emphasis>updatable</emphasis> - if present indicates that the cached results can be updated. This is currently only applicable to materialized views. + </para></listitem> + </itemizedlist> + </para> + <section> + <title>Limitations</title> + <itemizedlist> + <listitem><para>The form of the query hint must be matched exactly for the hint to have affect. + For a user query if the hint is not specified correctly, e.g. <code>/*+ cach(pref_mem) */</code>, it will not be used by the engine nor will + there be an informational log. As a workaround, the query plan may be checked though (see the Client Developers Guide) to see if the user command + in the plan has retained the proper hint. + </para></listitem> + </itemizedlist> + </section> + </section> + <section id="nocache"> + <title>OPTION NOCACHE</title> + <para>Individual queries may override the use of cached results by + specifying <code>OPTION NOCACHE</code> on the query.  0 or more fully qualified view or procedure names may be specified to exclude using their cached results.   + If no names are specified, cached results will not be used transitively. + </para> + <example> + <title>Full NOCACHE</title> + <programlisting>SELECT * from vg1, vg2, vg3 WHERE … OPTION NOCACHE</programlisting> + <para>No cached results will be used at all.</para> + </example> + <example> + <title>Specific NOCACHE</title> + <programlisting>SELECT * from vg1, vg2, vg3 WHERE … OPTION NOCACHE vg1, vg3</programlisting> + <para>Only the vg1 and vg3 caches will be skipped, vg2 or any cached results nested under vg1 and vg3 will be used.</para> + </example> + <para><code>OPTION NOCACHE</code> may be specified in procedure or view definitions.  In that way, transformations can + specify to always use real-time data obtained directly from sources. + </para> + </section> +</chapter> \ No newline at end of file Property changes on: branches/7.1.x/documentation/caching-guide/src/main/docbook/en-US/content/hint-option.xml ___________________________________________________________________ Name: svn:mime-type + text/plain Modified: branches/7.1.x/documentation/caching-guide/src/main/docbook/en-US/content/matviews.xml =================================================================== --- branches/7.1.x/documentation/caching-guide/src/main/docbook/en-US/content/matviews.xml 2010-08-25 17:56:09 UTC (rev 2491) +++ branches/7.1.x/documentation/caching-guide/src/main/docbook/en-US/content/matviews.xml 2010-08-26 15:29:47 UTC (rev 2492) @@ -27,35 +27,14 @@ </listitem> <listitem> <para>User ability to override use of materialized view cache for - specific queries through OPTION NO CACHE</para> + specific queries through <code>OPTION NOCACHE</code></para> </listitem> </itemizedlist> </section> <section> <title>User Interaction</title> - <para>When client applications issue queries against a Relational - table or view that has been defined as a materialized view, the - Teiid query engine automatically routes that query to obtain the - results from the cache database.</para> - <para>Individual queries may override the use of materialized views by - specifying OPTION NOCACHE on the query.  This parameter may specify - one or more virtual groups to override (separated by commas, spaces - optional).  If no virtual groups are specified, materialized views tables will not be used transitively. - </para> - <example> - <title>Full NOCACHE</title> - <programlisting>SELECT * from vg1, vg2, vg3 WHERE … OPTION NOCACHE</programlisting> - </example> - <example> - <title>Specific NOCACHE</title> - <programlisting>SELECT * from vg1, vg2, vg3 WHERE … OPTION NOCACHE vg1, vg3</programlisting> - <para>Only the vg1 and vg3 caches will be skipped vg2 or any materialized views nested under vg1 and vg3 will be used.</para> - </example> - <para>Option NOCACHE may be specified in virtual - group transformation queries.  In that way, transformations can - specify to always use real-time data obtained directly from a source. -  The use of caching and non-caching can be mixed in transformation - definitions, just as with user queries.</para> + <para>Similar to cached procedures, materialized view tables are used automatically when a query accesses the corresponding view. + 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> <title>Materialized View Definition</title> @@ -77,6 +56,9 @@ </listitem> </itemizedlist> </para> + <note><para>Materialized view tables are always scoped to the VDB. If a materialized view definition directly or transitively contains a non-deterministic + function call, such as random or hasRole, the resulting table will contain only the initially evaluated values. In most instances you should consider + nesting a materialized view without the deterministic results that is joined with relevant non-deterministic values in a parent view.</para></note> </section> <section> <title>External Materialization</title> @@ -112,6 +94,7 @@ <para>Internal materialization creates Teiid temporary tables to hold the materialized table. While these tables are not fully durable, they perform well in most circumstances and the data is present at each Teiid instance which removes the single point of failure and network overhead of an external database. Internal materialization also provides more built-in facilities for refreshing and monitoring.</para> + <para>The cache hint, when used in the context of an internal materialized view transformation query, provides the ability to fine tune the materializated table. The pref_mem option also applies to internal materialized views. Internal table index pages already have a memory preference, so the perf_mem option indicates that the data pages should prefer memory as well.</para> <section> <title>Loading And Refreshing</title> <para>An internal materialized view table is initially in an invalid state (there is no data). The first user query will trigger an implicit loading of the data. @@ -128,27 +111,47 @@ subsequent refreshes performed with <code>refreshMatView</code> will use dependent materialized view tables if they exist. Only one load may occur at a time. If a load is already in progress when the <code>SYS.refreshMatView</code> procedure is called, it will return -1 immediately rather than preempting the current load. </para> - <para>The <link linkend="cache-hint">cache hint</link> may be used to automatically trigger a full snapshot refresh after a specified time to live. - <example> - <title>Auto-refresh Transformation Query</title> - <programlisting>/*+ cache(ttl:3600000) */ select t.col, t1.col from t, t1 where t.id = t1.id</programlisting> - </example> - </para> - <para>In advanced use-cases the <link linkend="cache-hint">cache hint</link> may also be used to mark an internal materialized view as updatable. - An updatable internal materialized view may use the <code>SYS.refreshMatViewRow</code> procedure to update a single row in the materialized table. - To be updatable the materialized view must have a single column primary key. Composite keys are not yet supported by <code>SYS.refreshMatViewRow</code>. - <example> - <title>Updatable Scenario</title> - <para>Transofrmation Query: <programlisting>/*+ cache(updatable) */ select t.col, t1.col from t, t1 where t.id = t1.id</programlisting></para> - <para>Update: <programlisting>CALL SYS.updateMatViewRow(viewname=>'schema.matview', key=>5)</programlisting></para> - <para>Given that the schema.matview defines interger column col as it's primary key, the update will check the live source(s) for the row values. - If it exists, the materialized view table row will be updated. If it does not exist the correpsonding row will be deleted.</para> - </example> - The update query will not use dependent materialized view tables, so care should be taken to ensure that getting a single - row from this transformation query performs well. This may require the use of depedent join hints. - When the updatable option is not specified, accessing the materialized view table is more efficient because modifications do not need to be considered. - Therefore, only specify the updatable option if row based incremental updates are needed. Even when performing row updates, full snapshot refreshes may be needed to ensure consistency. - </para> + <section> + <title>TTL Snapshot Refresh</title> + <para>The <link linkend="cache-hint">cache hint</link> may be used to automatically trigger a full snapshot refresh after a specified time to live. + The refresh is equivalent to <code>CALL SYS.refreshMatView('view name', false)</code>, but performed asynchronously so that user queries do not block on the load.</para> + <example> + <title>Auto-refresh Transformation Query</title> + <programlisting>/*+ cache(ttl:3600000) */ select t.col, t1.col from t, t1 where t.id = t1.id</programlisting> + <para>The resulting materialized view will be reloaded every hour (3600000 milliseconds).</para> + </example> + <section> + <title>Limitations</title> + <itemizedlist> + <listitem><para>The automatic ttl refresh is not intended for complex loading scenarios, as nested materialized views will be used by the refresh query. + </para></listitem> + <listitem><para>The automatic ttl refresh is performed lazily, that is it is only trigger by using the table after the ttl has expired. + For infrequently used tables with long load times, this means that data may be used well past the intended ttl. + </para></listitem> + </itemizedlist> + </section> + </section> + <section> + <title>Updatable</title> + <para>In advanced use-cases the <link linkend="cache-hint">cache hint</link> may also be used to mark an internal materialized view as updatable. + An updatable internal materialized view may use the <code>SYS.refreshMatViewRow</code> procedure to update a single row in the materialized table. + If the source row exists, the materialized view table row will be updated. If the source row does not exist, the correpsonding materialized row will be deleted. + To be updatable the materialized view must have a single column primary key. Composite keys are not yet supported by <code>SYS.refreshMatViewRow</code>. + <example> + <title>Updatable Transformation Query</title> + <para>Transofrmation Query: <programlisting>/*+ cache(updatable) */ select t.col, t1.col from t, t1 where t.id = t1.id</programlisting></para> + <para>Update SQL: <programlisting>CALL SYS.updateMatViewRow(viewname=>'schema.matview', key=>5)</programlisting></para> + <para>Given that the schema.matview defines an interger column col as it's primary key, the update will check the live source(s) for the row values.</para> + </example> + The update query will not use dependent materialized view tables, so care should be taken to ensure that getting a single + row from this transformation query performs well. See the Reference Guide for information on controlling dependent joins, which may be applicable to increasing the performance of retrieving a single row. + The refresh query does use nested caches, so this refresh method should be used with caution. + </para> + <para> + When the updatable option is not specified, accessing the materialized view table is more efficient because modifications do not need to be considered. + Therefore, only specify the updatable option if row based incremental updates are needed. Even when performing row updates, full snapshot refreshes may be needed to ensure consistency. + </para> + </section> </section> <section> <title>Limitations</title> @@ -157,4 +160,4 @@ </itemizedlist> </section> </section> -</chapter> \ No newline at end of file +</chapter> Copied: branches/7.1.x/documentation/caching-guide/src/main/docbook/en-US/content/results.xml (from rev 2478, branches/7.1.x/documentation/caching-guide/src/main/docbook/en-US/content/resultset.xml) =================================================================== --- branches/7.1.x/documentation/caching-guide/src/main/docbook/en-US/content/results.xml (rev 0) +++ branches/7.1.x/documentation/caching-guide/src/main/docbook/en-US/content/results.xml 2010-08-26 15:29:47 UTC (rev 2492) @@ -0,0 +1,138 @@ +<!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> + </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>Non-XML document model results, BLOBs, CLOBs, and OBJECT typed values cannot be cached.</para> + </listitem> + <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> \ No newline at end of file Property changes on: branches/7.1.x/documentation/caching-guide/src/main/docbook/en-US/content/results.xml ___________________________________________________________________ Name: svn:mime-type + text/plain Deleted: branches/7.1.x/documentation/caching-guide/src/main/docbook/en-US/content/resultset.xml =================================================================== --- branches/7.1.x/documentation/caching-guide/src/main/docbook/en-US/content/resultset.xml 2010-08-25 17:56:09 UTC (rev 2491) +++ branches/7.1.x/documentation/caching-guide/src/main/docbook/en-US/content/resultset.xml 2010-08-26 15:29:47 UTC (rev 2492) @@ -1,93 +0,0 @@ -<!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="resultset"> - <title>Result Set Caching</title> - <para>Teiid provides the capability to cache the results of - specific user queries.  When the - exact same user query is submitted to Teiid, the - cached results will be returned.  This caching technique - can yield significant performance gains if users of the system submit - the same queries often.</para> - <para>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> - <section> - <title>Support Summary</title> - <itemizedlist> - <listitem> - <para>Caching of user query results.</para> - </listitem> - <listitem> - <para>Scoping of results is automatically determined to be either VDB (replicated) or session level.</para> - </listitem> - <listitem> - <para>Caching of XML document model results.</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> - <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> - To control the preference of individual results to remain in memory or the time to live see the <link linkend="cache-hint">cache hint</link>. - If a cache hint is not specified, then the default time to live of the result set caching configuration will be used. - </para> - <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>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> - </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>Non-XML document model results, BLOBs, and CLOBs cannot be cached.</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 result set cache clears all cache entries for all VDBs.</para> - </listitem> - </itemizedlist> - </section> -</chapter> \ No newline at end of file Modified: branches/7.1.x/documentation/caching-guide/src/main/docbook/en-US/main.xml =================================================================== --- branches/7.1.x/documentation/caching-guide/src/main/docbook/en-US/main.xml 2010-08-25 17:56:09 UTC (rev 2491) +++ branches/7.1.x/documentation/caching-guide/src/main/docbook/en-US/main.xml 2010-08-26 15:29:47 UTC (rev 2492) @@ -47,8 +47,8 @@ </bookinfo> <toc /> <xi:include href="content/overview.xml" xmlns:xi="http://www.w3.org/2001/XInclude" /> - <xi:include href="content/resultset.xml" xmlns:xi="http://www.w3.org/2001/XInclude" /> + <xi:include href="content/results.xml" xmlns:xi="http://www.w3.org/2001/XInclude" /> + <xi:include href="content/matviews.xml" xmlns:xi="http://www.w3.org/2001/XInclude" /> <xi:include href="content/codetable.xml" xmlns:xi="http://www.w3.org/2001/XInclude" /> - <xi:include href="content/matviews.xml" xmlns:xi="http://www.w3.org/2001/XInclude" /> - <xi:include href="content/cachehint.xml" xmlns:xi="http://www.w3.org/2001/XInclude" /> + <xi:include href="content/hint-option.xml" xmlns:xi="http://www.w3.org/2001/XInclude" /> </book> \ No newline at end of file Modified: branches/7.1.x/documentation/reference/src/main/docbook/en-US/content/sql_support.xml =================================================================== --- branches/7.1.x/documentation/reference/src/main/docbook/en-US/content/sql_support.xml 2010-08-25 17:56:09 UTC (rev 2491) +++ branches/7.1.x/documentation/reference/src/main/docbook/en-US/content/sql_support.xml 2010-08-26 15:29:47 UTC (rev 2492) @@ -1126,9 +1126,10 @@ <para>OPTION NOCACHE</para> </listitem> </itemizedlist> - <para>Previous versions of Teiid accepted the PLANONLY, DEBUG, and SHOWPLAN option arguments. These are no longer accepted in the OPTION clause. + <para>All tables specified in the OPTION clause should be fully qualified.</para> + <note><para>Previous versions of Teiid accepted the PLANONLY, DEBUG, and SHOWPLAN option arguments. These are no longer accepted in the OPTION clause. Please see the Client Developers Guide for replacements to those options. - </para> + </para></note> </section> </section> <section id="set_operations"> Modified: branches/7.1.x/engine/src/main/java/org/teiid/cache/DefaultCache.java =================================================================== --- branches/7.1.x/engine/src/main/java/org/teiid/cache/DefaultCache.java 2010-08-25 17:56:09 UTC (rev 2491) +++ branches/7.1.x/engine/src/main/java/org/teiid/cache/DefaultCache.java 2010-08-26 15:29:47 UTC (rev 2492) @@ -139,7 +139,11 @@ if (ttl < 0) { return Long.MAX_VALUE; } - return System.currentTimeMillis() + ttl; + long result = System.currentTimeMillis() + ttl; + if (result < ttl) { + result = Long.MAX_VALUE; + } + return result; } public V put(K key, V value, Long timeToLive) { Modified: branches/7.1.x/engine/src/main/java/org/teiid/cache/DefaultCacheFactory.java =================================================================== --- branches/7.1.x/engine/src/main/java/org/teiid/cache/DefaultCacheFactory.java 2010-08-25 17:56:09 UTC (rev 2491) +++ branches/7.1.x/engine/src/main/java/org/teiid/cache/DefaultCacheFactory.java 2010-08-26 15:29:47 UTC (rev 2492) @@ -34,8 +34,12 @@ private volatile boolean destroyed = false; public DefaultCacheFactory() { - this.cacheRoot = new DefaultCache("Teiid"); //$NON-NLS-1$ + this(CacheConfiguration.DEFAULT); } + + public DefaultCacheFactory(CacheConfiguration config) { + this.cacheRoot = new DefaultCache("Teiid", config.getMaxEntries(), config.getMaxAgeInSeconds()*1000); //$NON-NLS-1$ + } @Override public void destroy() { Modified: branches/7.1.x/engine/src/main/java/org/teiid/dqp/internal/process/CachedResults.java =================================================================== --- branches/7.1.x/engine/src/main/java/org/teiid/dqp/internal/process/CachedResults.java 2010-08-25 17:56:09 UTC (rev 2491) +++ branches/7.1.x/engine/src/main/java/org/teiid/dqp/internal/process/CachedResults.java 2010-08-26 15:29:47 UTC (rev 2492) @@ -88,6 +88,10 @@ this.hint = command.getCacheHint(); } + public void setHint(CacheHint hint) { + this.hint = hint; + } + public CacheHint getHint() { return hint; } Modified: branches/7.1.x/engine/src/main/java/org/teiid/dqp/internal/process/DQPCore.java =================================================================== --- branches/7.1.x/engine/src/main/java/org/teiid/dqp/internal/process/DQPCore.java 2010-08-25 17:56:09 UTC (rev 2491) +++ branches/7.1.x/engine/src/main/java/org/teiid/dqp/internal/process/DQPCore.java 2010-08-26 15:29:47 UTC (rev 2492) @@ -339,7 +339,7 @@ request.initialize(requestMsg, bufferManager, dataTierMgr, transactionService, state.sessionTables, workContext, this.useEntitlements, this.prepPlanCache); - + request.setResultSetCacheEnabled(this.rsCache != null); ResultsFuture<ResultsMessage> resultsFuture = new ResultsFuture<ResultsMessage>(); RequestWorkItem workItem = new RequestWorkItem(this, requestMsg, request, resultsFuture.getResultsReceiver(), requestID, workContext); logMMCommand(workItem, Event.NEW, null); @@ -657,7 +657,7 @@ this.processWorkerPool = new ThreadReuseExecutor(DQPConfiguration.PROCESS_PLAN_QUEUE_NAME, config.getMaxThreads()); dataTierMgr = new TempTableDataManager(new DataTierManagerImpl(this, - this.bufferService), this.bufferManager, this.processWorkerPool); + this.bufferService), this.bufferManager, this.processWorkerPool, this.rsCache); } public void setBufferService(BufferService service) { Modified: branches/7.1.x/engine/src/main/java/org/teiid/dqp/internal/process/Request.java =================================================================== --- branches/7.1.x/engine/src/main/java/org/teiid/dqp/internal/process/Request.java 2010-08-25 17:56:09 UTC (rev 2491) +++ branches/7.1.x/engine/src/main/java/org/teiid/dqp/internal/process/Request.java 2010-08-26 15:29:47 UTC (rev 2492) @@ -131,6 +131,7 @@ protected boolean useEntitlements; private TempTableStore globalTables; private SessionAwareCache<PreparedPlan> planCache; + private boolean resultSetCacheEnabled = true; void initialize(RequestMessage requestMsg, BufferManager bufferManager, @@ -161,6 +162,10 @@ this.metadata = metadata; this.multiSourceModels = multiSourceModels; } + + public void setResultSetCacheEnabled(boolean resultSetCacheEnabled) { + this.resultSetCacheEnabled = resultSetCacheEnabled; + } /** * if the metadata has not been supplied via setMetadata, this method will create the appropriate state @@ -260,6 +265,7 @@ context.setMetadata(this.metadata); context.setBufferManager(this.bufferManager); context.setPreparedPlanCache(planCache); + context.setResultSetCacheEnabled(this.resultSetCacheEnabled); } protected void checkReferences(List<Reference> references) throws QueryValidatorException { Modified: branches/7.1.x/engine/src/main/java/org/teiid/dqp/internal/process/SessionAwareCache.java =================================================================== --- branches/7.1.x/engine/src/main/java/org/teiid/dqp/internal/process/SessionAwareCache.java 2010-08-25 17:56:09 UTC (rev 2491) +++ branches/7.1.x/engine/src/main/java/org/teiid/dqp/internal/process/SessionAwareCache.java 2010-08-26 15:29:47 UTC (rev 2492) @@ -35,6 +35,7 @@ import org.teiid.cache.DefaultCacheFactory; import org.teiid.cache.CacheConfiguration.Policy; import org.teiid.common.buffer.BufferManager; +import org.teiid.core.types.DataTypeManager; import org.teiid.core.util.EquivalenceUtil; import org.teiid.core.util.HashCodeUtil; import org.teiid.query.function.metadata.FunctionMethod; @@ -46,7 +47,7 @@ * This class is used to cache session aware objects */ public class SessionAwareCache<T> { - public static final int DEFAULT_MAX_SIZE_TOTAL = 250; + public static final int DEFAULT_MAX_SIZE_TOTAL = 512; private Cache<CacheID, T> localCache; private Cache<CacheID, T> distributedCache; @@ -58,7 +59,7 @@ private BufferManager bufferManager; public SessionAwareCache(){ - this(new DefaultCacheFactory(), Cache.Type.RESULTSET, new CacheConfiguration(Policy.LRU, 60, DEFAULT_MAX_SIZE_TOTAL)); + this(DEFAULT_MAX_SIZE_TOTAL); } SessionAwareCache(int maxSize){ @@ -188,16 +189,28 @@ this.sessionId = sessionId; } + /** + * Set the raw (non-Constant) parameter values. + * @param parameters + * @return + */ public boolean setParameters(List<?> parameters) { if (parameters != null && !parameters.isEmpty()) { this.parameters = new ArrayList<Serializable>(); for (Object obj:parameters) { - if (obj instanceof Serializable && obj instanceof Comparable<?>) { - this.parameters.add((Serializable)obj); + if (obj == null) { + this.parameters.add(null); + continue; } - else{ + if (!(obj instanceof Serializable)) { return false; } + + Class<?> type = DataTypeManager.determineDataTypeClass(obj); + if (DataTypeManager.isLOB(type) || type == DataTypeManager.DefaultDataClasses.OBJECT) { + return false; + } + this.parameters.add((Serializable)obj); } } return true; Modified: branches/7.1.x/engine/src/main/java/org/teiid/query/optimizer/relational/RelationalPlanner.java =================================================================== --- branches/7.1.x/engine/src/main/java/org/teiid/query/optimizer/relational/RelationalPlanner.java 2010-08-25 17:56:09 UTC (rev 2491) +++ branches/7.1.x/engine/src/main/java/org/teiid/query/optimizer/relational/RelationalPlanner.java 2010-08-26 15:29:47 UTC (rev 2492) @@ -483,6 +483,22 @@ } } if (c != null) { + if (c.getCacheHint() != null) { + if (container instanceof StoredProcedure) { + boolean noCache = isNoCacheGroup(metadata, ((StoredProcedure) container).getProcedureID(), option); + if (!noCache) { + if (container.areResultsCachable() && Query.areResultsCachable(container.getProcedureParameters().keySet()) && context.isResultSetCacheEnabled()) { + container.getGroup().setGlobalTable(true); + container.setCacheHint(c.getCacheHint()); + recordAnnotation(analysisRecord, Annotation.MATERIALIZED_VIEW, Priority.LOW, "SimpleQueryResolver.procedure_cache_used", container.getGroup()); //$NON-NLS-1$ + return; + } + recordAnnotation(analysisRecord, Annotation.MATERIALIZED_VIEW, Priority.MEDIUM, "SimpleQueryResolver.procedure_cache_not_usable", container.getGroup()); //$NON-NLS-1$ + } else { + recordAnnotation(analysisRecord, Annotation.MATERIALIZED_VIEW, Priority.LOW, "SimpleQueryResolver.procedure_cache_not_used", container.getGroup()); //$NON-NLS-1$ + } + } + } //skip the rewrite here, we'll do that in the optimizer //so that we know what the determinism level is. addNestedCommand(sourceNode, container.getGroup(), container, c, false); @@ -925,14 +941,14 @@ //not use cache qnode = metadata.getVirtualPlan(metadataID); //TODO: update the table for defaultMat - recordMaterializationTableAnnotation(analysisRecord, QueryPlugin.Util.getString("SimpleQueryResolver.materialized_table_not_used", virtualGroup, matTableName)); //$NON-NLS-1$ + recordAnnotation(analysisRecord, Annotation.MATERIALIZED_VIEW, Priority.LOW, "SimpleQueryResolver.materialized_table_not_used", virtualGroup, matTableName); //$NON-NLS-1$ }else{ qnode = new QueryNode(groupName, null); Query query = createMatViewQuery(matMetadataId, matTableName, Arrays.asList(new AllSymbol()), isImplicitGlobal); query.setCacheHint(hint); qnode.setCommand(query); cacheString = "matview"; //$NON-NLS-1$ - recordMaterializationTableAnnotation(analysisRecord, QueryPlugin.Util.getString("SimpleQueryResolver.Query_was_redirected_to_Mat_table", virtualGroup, matTableName)); //$NON-NLS-1$ + recordAnnotation(analysisRecord, Annotation.MATERIALIZED_VIEW, Priority.LOW, "SimpleQueryResolver.Query_was_redirected_to_Mat_table", virtualGroup, matTableName); //$NON-NLS-1$ } } else { // Not a materialized view - query the primary transformation @@ -981,13 +997,13 @@ Command c = getCommand(table, metadata.getVirtualPlan(table.getMetadataID()), SQLConstants.Reserved.SELECT, metadata, analysisRecord); CacheHint hint = c.getCacheHint(); if (hint != null) { - recordMaterializationTableAnnotation(analysisRecord, QueryPlugin.Util.getString("SimpleQueryResolver.cache_hint_used", table, matTableName, hint)); //$NON-NLS-1$ + recordAnnotation(analysisRecord, Annotation.MATERIALIZED_VIEW, Priority.LOW, "SimpleQueryResolver.cache_hint_used", table, matTableName, id.getCacheHint()); //$NON-NLS-1$ } id.setCacheHint(hint); } } } else if (id.getCacheHint() != null) { - recordMaterializationTableAnnotation(analysisRecord, QueryPlugin.Util.getString("SimpleQueryResolver.cache_hint_used", table, matTableName, id.getCacheHint())); //$NON-NLS-1$ + recordAnnotation(analysisRecord, Annotation.MATERIALIZED_VIEW, Priority.LOW, "SimpleQueryResolver.cache_hint_used", table, matTableName, id.getCacheHint()); //$NON-NLS-1$ } return id; } @@ -1042,15 +1058,14 @@ return false; } - private static void recordMaterializationTableAnnotation(AnalysisRecord analysis, - String msg) { - if ( analysis.recordAnnotations() ) { - Annotation annotation = new Annotation(Annotation.MATERIALIZED_VIEW, - msg, - null, - Priority.LOW); - analysis.addAnnotation(annotation); - } + private static void recordAnnotation(AnalysisRecord analysis, String type, Priority priority, String msgKey, Object... parts) { + if (analysis.recordAnnotations()) { + Annotation annotation = new Annotation(type, + QueryPlugin.Util.getString(msgKey, parts), + null, + priority); + analysis.addAnnotation(annotation); + } } } \ No newline at end of file Modified: branches/7.1.x/engine/src/main/java/org/teiid/query/tempdata/TempTableDataManager.java =================================================================== --- branches/7.1.x/engine/src/main/java/org/teiid/query/tempdata/TempTableDataManager.java 2010-08-25 17:56:09 UTC (rev 2491) +++ branches/7.1.x/engine/src/main/java/org/teiid/query/tempdata/TempTableDataManager.java 2010-08-26 15:29:47 UTC (rev 2492) @@ -24,6 +24,7 @@ import java.util.ArrayList; import java.util.Arrays; +import java.util.LinkedList; import java.util.List; import java.util.concurrent.Callable; import java.util.concurrent.Executor; @@ -36,12 +37,16 @@ import org.teiid.api.exception.query.QueryValidatorException; import org.teiid.common.buffer.BlockedException; import org.teiid.common.buffer.BufferManager; +import org.teiid.common.buffer.TupleBuffer; import org.teiid.common.buffer.TupleSource; import org.teiid.core.CoreConstants; import org.teiid.core.TeiidComponentException; import org.teiid.core.TeiidProcessingException; import org.teiid.core.types.DataTypeManager; import org.teiid.core.util.StringUtil; +import org.teiid.dqp.internal.process.CachedResults; +import org.teiid.dqp.internal.process.SessionAwareCache; +import org.teiid.dqp.internal.process.SessionAwareCache.CacheID; import org.teiid.language.SQLConstants.Reserved; import org.teiid.logging.LogConstants; import org.teiid.logging.LogManager; @@ -52,6 +57,7 @@ import org.teiid.query.metadata.QueryMetadataInterface; import org.teiid.query.metadata.TempMetadataID; import org.teiid.query.optimizer.relational.RelationalPlanner; +import org.teiid.query.parser.ParseInfo; import org.teiid.query.processor.BatchCollector; import org.teiid.query.processor.CollectionTupleSource; import org.teiid.query.processor.ProcessorDataManager; @@ -65,8 +71,10 @@ import org.teiid.query.sql.lang.Delete; import org.teiid.query.sql.lang.Drop; import org.teiid.query.sql.lang.Insert; +import org.teiid.query.sql.lang.Option; import org.teiid.query.sql.lang.ProcedureContainer; import org.teiid.query.sql.lang.Query; +import org.teiid.query.sql.lang.SPParameter; import org.teiid.query.sql.lang.StoredProcedure; import org.teiid.query.sql.lang.Update; import org.teiid.query.sql.navigator.PostOrderNavigator; @@ -93,7 +101,7 @@ private ProcessorDataManager processorDataManager; private BufferManager bufferManager; - + private SessionAwareCache<CachedResults> cache; private Executor executor; public TempTableDataManager(ProcessorDataManager processorDataManager, BufferManager bufferManager) { @@ -102,7 +110,7 @@ public void execute(Runnable command) { command.run(); } - }); + }, new SessionAwareCache<CachedResults>()); } /** @@ -110,11 +118,13 @@ * and will pass most calls through to transparently. Only when a request is registered for * a temp group will this proxy do it's thing. * @param processorDataManager the real ProcessorDataManager that this object is a proxy to + * @param cache */ - public TempTableDataManager(ProcessorDataManager processorDataManager, BufferManager bufferManager, Executor executor){ + public TempTableDataManager(ProcessorDataManager processorDataManager, BufferManager bufferManager, Executor executor, SessionAwareCache<CachedResults> cache){ this.processorDataManager = processorDataManager; this.bufferManager = bufferManager; this.executor = executor; + this.cache = cache; } public TupleSource registerRequest( @@ -141,11 +151,49 @@ return registerQuery(context, contextStore, query); } if (command instanceof ProcedureContainer) { - - if (command instanceof StoredProcedure && CoreConstants.SYSTEM_MODEL.equals(modelName)) { - TupleSource result = handleSystemProcedures(context, command); - if (result != null) { - return result; + if (command instanceof StoredProcedure) { + StoredProcedure proc = (StoredProcedure)command; + if (CoreConstants.SYSTEM_MODEL.equals(modelName)) { + TupleSource result = handleSystemProcedures(context, proc); + if (result != null) { + return result; + } + } else if (proc.getGroup().isGlobalTable()) { + String fullName = context.getMetadata().getFullName(proc.getProcedureID()); + LinkedList<Object> vals = new LinkedList<Object>(); + for (SPParameter param : proc.getInputParameters()) { + vals.add(((Constant)param.getExpression()).getValue()); + } + //collapse the hash to single byte for the key to restrict the possible results to 256 + int hash = vals.hashCode(); + hash |= (hash >>> 16); + hash |= (hash >>> 8); + hash &= 0x000000ff; + CacheID cid = new CacheID(new ParseInfo(), fullName + hash, context.getVdbName(), + context.getVdbVersion(), context.getConnectionID(), context.getUserName()); + cid.setParameters(vals); + CachedResults results = cache.get(cid); + if (results != null) { + TupleBuffer buffer = results.getResults(); + return buffer.createIndexedTupleSource(); + } + CacheHint hint = proc.getCacheHint(); + proc.setCacheHint(null); + Option option = new Option(); + option.setNoCache(true); + option.addNoCacheGroup(fullName); + proc.setOption(option); + int determinismLevel = context.resetDeterminismLevel(); + QueryProcessor qp = context.getQueryProcessorFactory().createQueryProcessor(proc.toString(), fullName.toUpperCase(), context); + qp.setNonBlocking(true); + BatchCollector bc = qp.createBatchCollector(); + TupleBuffer tb = bc.collectTuples(); + CachedResults cr = new CachedResults(); + cr.setResults(tb); + cr.setHint(hint); + cache.put(cid, context.getDeterminismLevel(), cr, hint != null?hint.getTtl():null); + context.setDeterminismLevel(determinismLevel); + return tb.createIndexedTupleSource(); } } @@ -200,14 +248,13 @@ return null; } - private TupleSource handleSystemProcedures(CommandContext context, Command command) + private TupleSource handleSystemProcedures(CommandContext context, StoredProcedure proc) throws TeiidComponentException, QueryMetadataException, QueryProcessingException, QueryResolverException, QueryValidatorException, TeiidProcessingException, ExpressionEvaluationException { QueryMetadataInterface metadata = context.getMetadata(); TempTableStore globalStore = context.getGlobalTableStore(); - StoredProcedure proc = (StoredProcedure)command; if (StringUtil.endsWithIgnoreCase(proc.getProcedureCallableName(), REFRESHMATVIEW)) { Object groupID = validateMatView(metadata, proc); String matViewName = metadata.getFullName(groupID); @@ -361,10 +408,11 @@ } int rowCount = -1; try { + String fullName = metadata.getFullName(group.getMetadataID()); //TODO: order by primary key nulls first - then have an insert ordered optimization //TODO: use the getCommand logic in RelationalPlanner to reuse commands for this. String transformation = metadata.getVirtualPlan(group.getMetadataID()).getQuery(); - QueryProcessor qp = context.getQueryProcessorFactory().createQueryProcessor(transformation, group.getCanonicalName(), context); + QueryProcessor qp = context.getQueryProcessorFactory().createQueryProcessor(transformation, fullName, context); qp.setNonBlocking(true); TupleSource ts = new BatchCollector.BatchProducerTupleSource(qp); //TODO: if this insert fails, it's unnecessary to do the undo processing Modified: branches/7.1.x/engine/src/main/java/org/teiid/query/util/CommandContext.java =================================================================== --- branches/7.1.x/engine/src/main/java/org/teiid/query/util/CommandContext.java 2010-08-25 17:56:09 UTC (rev 2491) +++ branches/7.1.x/engine/src/main/java/org/teiid/query/util/CommandContext.java 2010-08-26 15:29:47 UTC (rev 2492) @@ -108,6 +108,9 @@ private TempTableStore globalTables; private SessionAwareCache<PreparedPlan> planCache; + + private boolean resultSetCacheEnabled = true; + } private GlobalState globalState = new GlobalState(); @@ -499,5 +502,13 @@ CacheID id = new CacheID(new ParseInfo(), key, getVdbName(), getVdbVersion(), getConnectionID(), getUserName()); this.globalState.planCache.put(id, determinismLevel, plan, null); } + + public boolean isResultSetCacheEnabled() { + return globalState.resultSetCacheEnabled; + } + + public void setResultSetCacheEnabled(boolean resultSetCacheEnabled) { + this.globalState.resultSetCacheEnabled = resultSetCacheEnabled; + } } Modified: branches/7.1.x/engine/src/main/resources/org/teiid/query/i18n.properties =================================================================== --- branches/7.1.x/engine/src/main/resources/org/teiid/query/i18n.properties 2010-08-25 17:56:09 UTC (rev 2491) +++ branches/7.1.x/engine/src/main/resources/org/teiid/query/i18n.properties 2010-08-26 15:29:47 UTC (rev 2492) @@ -864,6 +864,9 @@ ResolveVariablesVisitor.reserved_word_for_temporary_used=Cursor names cannot begin with "#" as that indicates the name of a temporary table: {0}. SimpleQueryResolver.materialized_table_not_used=The query against {0} did not use materialization table {1} due to the use of OPTION NOCACHE. SimpleQueryResolver.cache_hint_used=Loading materialized view {1} for view {0} using cache hint {2}. +SimpleQueryResolver.procedure_cache_used=Procedure caching will be used for {0}. +SimpleQueryResolver.procedure_cache_not_usable=Procedure caching will not be used for {0} since the result set cache is disabled or the results/parameters cannot be cached. +SimpleQueryResolver.procedure_cache_not_used=Procedure caching will not be used for {0} due to the use of OPTION NOCACHE. ValidationVisitor.input_variable_data_type_not_match=The expression "{0}" assigned to input variable "{1}" is of type "{2}" which cannot be implicitly converted to the expected type "{3}". ValidationVisitor.input_variable_can_not_in_function=Input variable "{0}" cannot be an argument of a function in the criteria. ValidationVisitor.groupby_subquery=Expressions used in a GROUP BY cannot be constant and must not contain subqueries: "{0}". Modified: branches/7.1.x/engine/src/test/java/org/teiid/query/processor/TestMaterialization.java =================================================================== --- branches/7.1.x/engine/src/test/java/org/teiid/query/processor/TestMaterialization.java 2010-08-25 17:56:09 UTC (rev 2491) +++ branches/7.1.x/engine/src/test/java/org/teiid/query/processor/TestMaterialization.java 2010-08-26 15:29:47 UTC (rev 2492) @@ -101,5 +101,18 @@ execute("SELECT * from vgroup4 where x is null", Arrays.asList((String)null)); assertEquals(2, hdm.getCommandHistory().size()); } + + @Test public void testProcedureCache() throws Exception { + execute("call sp1('one')", Arrays.asList("one")); + assertEquals(1, hdm.getCommandHistory().size()); + execute("call sp1('one')", Arrays.asList("one")); + assertEquals(1, hdm.getCommandHistory().size()); + execute("call sp1('one') option nocache sp.sp1", Arrays.asList("one")); + assertEquals(2, hdm.getCommandHistory().size()); + execute("call sp1(null)"); + assertEquals(3, hdm.getCommandHistory().size()); + execute("call sp1(null)"); + assertEquals(3, hdm.getCommandHistory().size()); + } } Modified: branches/7.1.x/engine/src/test/java/org/teiid/query/processor/TestProcessor.java =================================================================== --- branches/7.1.x/engine/src/test/java/org/teiid/query/processor/TestProcessor.java 2010-08-25 17:56:09 UTC (rev 2491) +++ branches/7.1.x/engine/src/test/java/org/teiid/query/processor/TestProcessor.java 2010-08-26 15:29:47 UTC (rev 2492) @@ -257,6 +257,7 @@ for (int i = 0; i < 100; i++) { try { id = collector.collectTuples(); + break; } catch (BlockedException e) { } Modified: branches/7.1.x/engine/src/test/java/org/teiid/query/unittest/RealMetadataFactory.java =================================================================== --- branches/7.1.x/engine/src/test/java/org/teiid/query/unittest/RealMetadataFactory.java 2010-08-25 17:56:09 UTC (rev 2491) +++ branches/7.1.x/engine/src/test/java/org/teiid/query/unittest/RealMetadataFactory.java 2010-08-26 15:29:47 UTC (rev 2492) @@ -358,6 +358,14 @@ createElements(vGroup4, new String[] { "x" }, //$NON-NLS-1$ new String[] { DataTypeManager.DefaultDataTypes.STRING}); + + Schema sp = createVirtualModel("sp", metadataStore); //$NON-NLS-1$ + ColumnSet<Procedure> rs = createResultSet("sp1.vsprs1", new String[] { "StringKey" }, new String[] { DataTypeManager.DefaultDataTypes.STRING }); //$NON-NLS-1$ //$NON-NLS-2$ + ProcedureParameter param = createParameter("param1", ParameterInfo.IN, DataTypeManager.DefaultDataTypes.STRING); //$NON-NLS-1$ + param.setNullType(NullType.Nullable); + QueryNode sp1qn = new QueryNode("sp1", "/*+ cache */ CREATE VIRTUAL PROCEDURE BEGIN SELECT x as StringKey from matsrc where x = param1; END"); //$NON-NLS-1$ //$NON-NLS-2$ + Procedure vsp5 = createVirtualProcedure("sp1", sp, Arrays.asList(param), sp1qn); //$NON-NLS-1$ + vsp5.setResultSet(rs); return createTransformationMetadata(metadataStore, ""); }