[JBoss JIRA] (TEIID-2892) OData buffers ALL rows from resultset before returning the first batch
by Ramesh Reddy (JIRA)
[ https://issues.jboss.org/browse/TEIID-2892?page=com.atlassian.jira.plugin... ]
Ramesh Reddy commented on TEIID-2892:
-------------------------------------
Did't we implement any such sorting already?
> 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%207...
> 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
10 years, 1 month
[JBoss JIRA] (TEIID-2892) OData buffers ALL rows from resultset before returning the first batch
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-2892?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-2892:
---------------------------------------
Some relevant docs: http://msdn.microsoft.com/en-us/library/dd942121.aspx
"If the data service URI contains a $skiptoken query option, but does not contain an $orderby option that identifies a full ordering of the collection of entities identified by the URI, then the entities in the set MUST first be fully ordered by the data service. Such a full order SHOULD be obtained by sorting the entities based on their EntityKey values. While no ordering semantics are mandated, a data service MUST always use the same semantics to obtain a full ordering across different requests on the same entity set. The syntax of the skip token system query option is defined as follows."
So it's not fully mandated that it must be sorted by the key, just that it must be consistently sorted.
> 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%207...
> 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
10 years, 1 month
[JBoss JIRA] (TEIID-2935) OData inline count is not correct when used with skip/top
by Steven Hawkins (JIRA)
Steven Hawkins created TEIID-2935:
-------------------------------------
Summary: OData inline count is not correct when used with skip/top
Key: TEIID-2935
URL: https://issues.jboss.org/browse/TEIID-2935
Project: Teiid
Issue Type: Bug
Components: OData
Affects Versions: 8.3
Reporter: Steven Hawkins
Assignee: Steven Hawkins
Fix For: 8.7.1, 8.8
Inline count should be computed prior to the application of top/skip. Currently we apply it afterwords (they are added to the root query on the limit clause).
--
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
10 years, 1 month
[JBoss JIRA] (TEIID-2934) OData improvements
by Steven Hawkins (JIRA)
Steven Hawkins created TEIID-2934:
-------------------------------------
Summary: OData improvements
Key: TEIID-2934
URL: https://issues.jboss.org/browse/TEIID-2934
Project: Teiid
Issue Type: Enhancement
Components: OData
Reporter: Steven Hawkins
Assignee: Steven Hawkins
Fix For: 8.7.1, 8.8
We should only use scrolling when performing caching and we should ensure that the cache entries are being created - either by reading the whole resultset or by adding read all functionality to the cache hint.
--
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
10 years, 1 month
[JBoss JIRA] (TEIID-2930) SCROLL keyword for DECLARE CURSOR not being parsed correctly
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-2930?page=com.atlassian.jira.plugin... ]
Steven Hawkins updated TEIID-2930:
----------------------------------
Fix Version/s: 7.7.9
> SCROLL keyword for DECLARE CURSOR not being parsed correctly
> ------------------------------------------------------------
>
> Key: TEIID-2930
> URL: https://issues.jboss.org/browse/TEIID-2930
> Project: Teiid
> Issue Type: Bug
> Components: ODBC
> Affects Versions: 7.7.8
> Reporter: Johnathon Lee
> Assignee: Steven Hawkins
> Fix For: 7.7.9, 8.7.1, 8.8
>
>
> The DECLARE cursor is defined as [1]. However, in [2] line #155, the SCROLL keyword is not being matched. Correction would be to match the pattern fully as defined in [1]
> [1]
> http://www.postgresql.org/docs/8.4/static/sql-declare.html
> [2]
> https://github.com/teiid/teiid/blob/master/runtime/src/main/java/org/teii...
> Example of error:
> TRACE [org.teiid.ODBC] (New I/O server worker #3-1) invoking server method: executeQuery [BEGIN;declare "SQL_CUR0x11ad4120" scroll cursor for <SQL QUERY>;fetch 100 in "SQL_CUR0x11ad4120"]
> DEBUG [org.teiid.ODBC] (New I/O server worker #3-1) Modified Query: START TRANSACTION
> FINER [org.teiid.jdbc] (New I/O server worker #3-1) Executing: requestID -1 commands: [START TRANSACTION] expecting: EITHER
> FINER [org.teiid.jdbc] (New I/O server worker #3-1) Executing as transaction statement
> TRACE [org.teiid.ODBC] (New I/O server worker #3-1) invoking client method: sendUpdateCount [BEGIN, 0]
> FINER [org.teiid.jdbc] (New I/O server worker #3-1) Executing: requestID -1 commands: [show client_encoding] expecting: RESULTSET
> FINER [org.teiid.jdbc] (New I/O server worker #3-1) Executing as show statement
> FINER [org.teiid.jdbc] (New I/O server worker #3-1) Creating ResultSet requestID: -1 beginRow: 1 resultsColumns: 1 parameters: 0
> TRACE [org.teiid.ODBC] (New I/O server worker #3-1) invoking client method: setEncoding [UTF-8]
> FINE [org.teiid.jdbc] (New I/O server worker #3-1) Statement closed successfully.
> DEBUG [org.teiid.ODBC] (New I/O server worker #3-1) Modified Query: declare "SQL_CUR0x11ad4120" scroll cursor for <SQL QUERY>
--
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
10 years, 1 month
[JBoss JIRA] (TEIID-2892) OData buffers ALL rows from resultset before returning the first batch
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-2892?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-2892:
---------------------------------------
I should have looked at this before, but the other thing that you are seeing Patrick is that your source does not support SORTING - however OData requires results in natural order. So all of your results from the root query must first be read to sort them. You'd have to see how to enabling sorting on your source, or log some sort of enhancement to allow odata to disregard ordering.
> 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%207...
> 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
10 years, 1 month
[JBoss JIRA] (TEIID-2892) OData buffers ALL rows from resultset before returning the first batch
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-2892?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-2892:
---------------------------------------
usecallingthread defaults to true.
Caching and scrolling are both used.
What I'm saying may nned more effort is to look at the effect of proactive full buffering on cached / scroll results and whether we need more logic to ensure the cache entries are being created.
> 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%207...
> 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
10 years, 1 month
[JBoss JIRA] (TEIID-2892) OData buffers ALL rows from resultset before returning the first batch
by Ramesh Reddy (JIRA)
[ https://issues.jboss.org/browse/TEIID-2892?page=com.atlassian.jira.plugin... ]
Ramesh Reddy commented on TEIID-2892:
-------------------------------------
Does not look like we are setting "useCallingThread". Also I remember initial design opted to use resultset caching, but then later we changed to scroll based cursor as much of functionality was similar and row count also could also be read.
Patrick: how did you come up 4x time? in JDBC the batching is done all internally and there may be pro-active buffering for next batch I believe. Did you read all the rows from the resultset? In OData, by default it only returns first 256 rows (with TEIID-2933) it may returned only 256 rows unless batch size is set to unlimited. In that case, I am not sure how efficient is HTTP.
> 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%207...
> 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
10 years, 1 month
[JBoss JIRA] (TEIID-2933) OData skipToken not properly honored
by Steven Hawkins (JIRA)
Steven Hawkins created TEIID-2933:
-------------------------------------
Summary: OData skipToken not properly honored
Key: TEIID-2933
URL: https://issues.jboss.org/browse/TEIID-2933
Project: Teiid
Issue Type: Bug
Components: OData
Affects Versions: 8.3
Reporter: Steven Hawkins
Assignee: Steven Hawkins
Fix For: 8.7.1, 8.8
If a skip token is provided, we still start providing results from the start of the result set.
--
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
10 years, 1 month