[teiid-issues] [JBoss JIRA] (TEIID-2981) Add an OData query parameter that caches the resultset

[John Muller (JIRA)]

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

John Muller commented on TEIID-2981:
------------------------------------

[~rareddy] Yes, I figured on both your points, but I don't see a way around extending the OData spec that still provides a result set cache over OData.

[~shawkins]  I figured there must be caching for skipToken / skip based pagination, thanks for confirming.  Our use case is a lengthy SQLServer stored procedure call that comes back with a medium-sized result set (1k to 2k rows).  We simply wrapped the physical SPROC in a virtual procedure and then wrapped that in a virtual table.  We don't even bother paginating and simply set the batch size to 2048.  The issue we're encountering is the length of time the SqlServer SPROC takes is too long and consuming apps tend to hit it repeatedly with the same query.  The SPROC has an IN parameter that is basically a user identifier and the underlying tables are much too big for internal materialization.  External materialization is an expensive option, but really we only need to cache the user-filtered end result of the SPROC call.  OData based caching would help greatly here, but the more generalized use case is for really small ReST queries that are executed repeatedly.  

> Add an OData query parameter that caches the resultset
> ------------------------------------------------------
>
>                 Key: TEIID-2981
>                 URL: https://issues.jboss.org/browse/TEIID-2981
>             Project: Teiid
>          Issue Type: Feature Request
>          Components: OData
>    Affects Versions: 8.7
>            Reporter: John Muller
>            Assignee: Steven Hawkins
>            Priority: Minor
>              Labels: Caching, OData,
>
> The Teiid caching guide indicates that the /*+ cache */ SQL hint is neccessary in order to cache results [1] .  We would like two features added to the OData support:  (1) to have a query parameter that basically injects the hint into the SQL statement run against the VDB and (2) a configuration setting that turns on OData resultset caching for all result sets smaller than some size (perhaps 256 as the default)



--
This message was sent by Atlassian JIRA
(v6.2.3#6260)