[teiid-issues] [JBoss JIRA] (TEIID-2892) OData buffers ALL rows from resultset before returning the first batch

[Ramesh Reddy (JIRA)]

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

Ramesh Reddy commented on TEIID-2892:
-------------------------------------

In OData protocol, you can request with offset in both directions, so I believe to support that Teiid used TYPE_SCROLL_INSENSITIVE cursor, so this will be keeping results in cache. Yes, as side effect it can also report correct row counts.  However, I am not sure why it would not return first batch until all rows are read.
                
> OData buffers ALL rows from resultset before returning the first batch
> ----------------------------------------------------------------------
>
>                 Key: TEIID-2892
>                 URL: https://issues.jboss.org/browse/TEIID-2892
>             Project: Teiid
>          Issue Type: Bug
>          Components: OData
>    Affects Versions: 8.4.1
>         Environment: Tested with Jboss DV 6.0.0. GA (enterprise edition) on Apple OSX 10.9.2 and Oracle Java VM 1.7.0_51.
>            Reporter: Patrick Deenen
>            Assignee: Steven Hawkins
>         Attachments: logfiles.zip
>
>
> OData doesn’t batch internally opposed to JDBC which does. E.g. when in JDBC a query is done with a large result, only the first 2048 rows are physically fetched from the source database and only the first 200 rows (depending on client application) are returned. But when the same query is executed by the use of Odata ALL rows in the result set are physically fetched by DV and stored in the buffer. Even with the default Odata fetch batch size of 256. This makes the Odata interface very inefficient for large query results where one only is interessed in the first 256 rows.
> Attached you can find two log files which show the problem. 
> The Odata query used is:
> http://localhost:8080/odata/PMA/EVENT_FACT?$filter=event_fact_id%20ge%20747000000%20and%20event_fact_id%20lt%20747200000
> Which is identical to the JDBC query used:
> select * from event_fact where event_fact_id between 747000000 and 747200000;
> In both cases the result contains 200.000 rows
> ODATA log information analysis (log file ’server start + odata batch 256.log’):
> row 4543 - 4657   - Start query
> row 4658 - 9030   - Read ALL results from result set and store them in buffer
> row 9031 - 9035   - Close DB connection
> row 9036 - 14647  - Clean buffers and create response?
> row 14648 - 14661 - return first batch and close connection
> JDBC log information analysis (log file ’server start + jdbc.log’):
> row 4925 - 5112   - Start query
> row 5113 - 5166   - Read ONLY the first 2048 results from result set and store them in buffer and return response
> row 5157 - 5214   - Close DB connection

--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira