[teiid-issues] [JBoss JIRA] Commented: (TEIID-168) Remove resultset caching

[Greg Haber (JIRA)]

    [ https://jira.jboss.org/jira/browse/TEIID-168?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12453352#action_12453352 ] 

Greg Haber commented on TEIID-168:
----------------------------------

Thanks for the info.  I think we are completely on the right track here by caching intermediate results rather than the end user result sets, but unfortunately I think that if the caching is done per-VM only that will greatly limit its usefulness (especially if the caching scope is the current session, rather than all sessions by that user to that VDB).

Why?  Because typically end users are not connecting directly to Teiid, but instead are connecting to it via a connection pool in their app server.  And often these apps will release these connections back to the pool after completing each query (it would be expensive to hold on to pooled connections in a web-based app where it is not always possible to reliably detect user logout).

So there is no guarantee that the same logical user will get the same connection from the pool, and since our connection logic randomly distributes connections among all the MMProcesses, this means that they are as likely to get a connection to a different JVM than they used for their previous query.

A similar problem exists with apps that connect straight to MetaMatrix but that close connections after queries (or that only pool for a short time), such as connections coming in over ODBC via an ODBC-JDBC gateway mechanism as in the commerical JBEDSP product.

So the usefulness of this caching mechanism will be limited to apps that use a legacy client-server type design where the client holds the connection open for an extended period of time - it won't help that much with modern multitier apps where the same logical user is likely to use several different sessions to MetaMatrix (potentially to different JVMs) in an application session.

> Remove resultset caching
> ------------------------
>
>                 Key: TEIID-168
>                 URL: https://jira.jboss.org/jira/browse/TEIID-168
>             Project: Teiid
>          Issue Type: Feature Request
>          Components: JDBC Connector, Query Engine
>    Affects Versions: 6.x
>            Reporter: Steven Hawkins
>            Assignee: Steven Hawkins
>             Fix For: 6.1.0
>
>
> Resultset caching should be replaced with relation and procedure caching.  This would initially be backed by the buffermanager, based upon hints/costing, and be utilized by the planner rather than the resolver.  It would be good if we also detected the level of determinism.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: https://jira.jboss.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira