[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:
---------------------------------------
We have another general issue in that the caching of the initial result may not happen in time before the connection is closed in which case the request will effectively be canceled. We may need to use the cache directive read all behavior - but unless the embedded connections are set to not use the calling thread the resultset.close will prevent the initial result from being sent until the cache entry is 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, 2 months
[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:
---------------------------------------
> The choice to use the scroll intensive resultset was more to support "skip" and "batching" functionality than the row count. Since sending all the results on the first request is not feasible, and there is no "easier" way to keep track of session for next set of results.
Actually I think the skip functionality is broken. Starting with d1a8d36696a3ada03cac7009cdd24bb2257cbff9 it doesn't appear that we are positioning the cursor at the skip location.
Generally then, there is no need to use forward only to support skip and batching as you would just manually iterate. The only reason for scrolling is to determine the inline count and then reposition the cursor. Subsequent requests are feed off of the cached or new result set, not off of the original so there isn't a need for further repositioning.
> One option could be we could by provide an option to turn on "forward-only" resultset through either
That should be unnecessary. Much of the distinction that I've made between forward-only vs. scroll is moot as when we cache we are also bypassing the flow control logic.
> Steve: I do not think I followed your comment below
I'm saying that much of the apparent performance beyond odata overhead is coming from the rate at which we let the plan build up data in the output buffer. So I will take a look at if we should slow down the rate at which scrolling, cached, or transactional output buffer results are collected.
> 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, 2 months
[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:
-------------------------------------
JDBC forward-only Vs scroll-intensive should give baseline numbers. Anything above is the odata overhead. Typically this involves marshaling of the resultset into OData output form and HTTP protocol weight. Never be equal to fully optimized binary JDBC protocol with forward-only cursor.
The choice to use the scroll intensive resultset was more to support "skip" and "batching" functionality than the row count. Since sending all the results on the first request is not feasible, and there is no "easier" way to keep track of session for next set of results. OData specification gives full details about the HTTP conversation, but never explicitly mentions how a connection/session can be maintained for duration of the query scrolling. Keeping the connection and resultset in cache per query is one option, but it was determined during the development time that it could lead memory exhaustion in situations like burst of in coming queries.
One option could be we could by provide an option to turn on "forward-only" resultset through either
* URL parameter (this will be custom extension)
* Provide as configuration property (Will apply to all the vdbs)
* As HEADER in HTTP call (also will be a custom extension)
* Cookies? (also would be custom extension)
We may need to do some investigation as how others are solving this issue?
There may be other ways. Any input is welcome.
Steve: I do not think I followed your comment below
>We also may need to revisit the data flow into output buffers even with a transaction or a scrolling result, as >allowing unfettered buffering may be an general problem
> 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, 2 months
[JBoss JIRA] (TEIID-2781) Unable to update database table with composite keys using OData
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-2781?page=com.atlassian.jira.plugin... ]
Steven Hawkins closed TEIID-2781.
---------------------------------
> Unable to update database table with composite keys using OData
> ---------------------------------------------------------------
>
> Key: TEIID-2781
> URL: https://issues.jboss.org/browse/TEIID-2781
> Project: Teiid
> Issue Type: Bug
> Components: SOAP Services
> Affects Versions: 8.4.1
> Reporter: Van Halbert
> Assignee: Steven Hawkins
> Fix For: 8.4.1, 8.7
>
> Attachments: odataerror.txt, post-screen-shot.png, Shell00990083.tar
>
>
> Using Postgres db, and created a table with composite keys.
> CREATE TABLE composite_key
> (
> ou_id character varying(3) NOT NULL,
> product_name character varying(15) NOT NULL,
> product_price numeric(10,2),
> CONSTRAINT composite_key_pkey PRIMARY KEY (ou_id, product_name)
> )
> WITH (
> OIDS=FALSE
> );
> ALTER TABLE composite_key OWNER TO postgres;
> INSERT INTO composite_key VALUES ('1','1',1);
> INSERT INTO composite_key VALUES ('1','2',1.20);
> INSERT INTO composite_key VALUES ('2','1',2.10);
> Deploy the VDB and use SOAP UI to do PUT (aka SQL UPDATE) generates attached odataerror.txt
--
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, 2 months
[JBoss JIRA] (TEIID-2823) Postgres is throwing PSQLExceptions, but Teiid is logging as warnings, and nothing is getting back to client
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-2823?page=com.atlassian.jira.plugin... ]
Steven Hawkins closed TEIID-2823.
---------------------------------
> Postgres is throwing PSQLExceptions, but Teiid is logging as warnings, and nothing is getting back to client
> ------------------------------------------------------------------------------------------------------------
>
> Key: TEIID-2823
> URL: https://issues.jboss.org/browse/TEIID-2823
> Project: Teiid
> Issue Type: Bug
> Components: JDBC Connector
> Affects Versions: 8.4
> Reporter: Van Halbert
> Assignee: Steven Hawkins
>
> Postgres is throwing PSQLExceptions, but Teiid is logging them as warning. The problem is the error isn't being returned to the client. The app continues to run with no knowledge of all the errors on the server. Here's an example of the server error:
> 09:50:35,349 WARN [org.teiid.CONNECTOR] (Worker6_QueryProcessorQueue12168) Connector worker process failed for atomic-request=Pn2BUCkOYts6.0.9.4084: org.teiid.translator.jdbc.JDBCExecutionException: 0 TEIID11013:TEIID11004 Error executing statement(s): [Prepared Values: [267, 2625077, 27, -280251] SQL: INSERT INTO "public"."history" ("tid", "bid", "aid", "delta") VALUES (?, ?, ?, ?)]
> at org.teiid.translator.jdbc.JDBCUpdateExecution.executeTranslatedCommand(JDBCUpdateExecution.java:242)
> at org.teiid.translator.jdbc.JDBCUpdateExecution.execute(JDBCUpdateExecution.java:76)
> at org.teiid.dqp.internal.datamgr.ConnectorWorkItem$1.execute(ConnectorWorkItem.java:345) [teiid-engine-8.4.1-redhat-7.jar:8.4.1-redhat-7]
> at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:312) [teiid-engine-8.4.1-redhat-7.jar:8.4.1-redhat-7]
> at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:301) [teiid-engine-8.4.1-redhat-7.jar:8.4.1-redhat-7]
> at org.teiid.dqp.internal.process.DataTierTupleSource.nextTuple(DataTierTupleSource.java:138) [teiid-engine-8.4.1-redhat-7.jar:8.4.1-redhat-7]
> at org.teiid.query.processor.relational.AccessNode.nextBatchDirect(AccessNode.java:306) [teiid-engine-8.4.1-redhat-7.jar:8.4.1-redhat-7]
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:278) [teiid-engine-8.4.1-redhat-7.jar:8.4.1-redhat-7]
> at org.teiid.query.processor.relational.RelationalPlan.nextBatch(RelationalPlan.java:149) [teiid-engine-8.4.1-redhat-7.jar:8.4.1-redhat-7]
> at org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:149) [teiid-engine-8.4.1-redhat-7.jar:8.4.1-redhat-7]
> at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:112) [teiid-engine-8.4.1-redhat-7.jar:8.4.1-redhat-7]
> at org.teiid.query.processor.BatchIterator.finalRow(BatchIterator.java:69) [teiid-engine-8.4.1-redhat-7.jar:8.4.1-redhat-7]
> at org.teiid.common.buffer.AbstractTupleSource.getCurrentTuple(AbstractTupleSource.java:70) [teiid-engine-8.4.1-redhat-7.jar:8.4.1-redhat-7]
> at org.teiid.query.processor.BatchIterator.getCurrentTuple(BatchIterator.java:84) [teiid-engine-8.4.1-redhat-7.jar:8.4.1-redhat-7]
> at org.teiid.common.buffer.AbstractTupleSource.hasNext(AbstractTupleSource.java:92) [teiid-engine-8.4.1-redhat-7.jar:8.4.1-redhat-7]
> at org.teiid.query.processor.proc.ProcedurePlan.executePlan(ProcedurePlan.java:563) [teiid-engine-8.4.1-redhat-7.jar:8.4.1-redhat-7]
> at org.teiid.query.processor.proc.CreateCursorResultSetInstruction.process(CreateCursorResultSetInstruction.java:68) [teiid-engine-8.4.1-redhat-7.jar:8.4.1-redhat-7]
> at org.teiid.query.processor.proc.ProcedurePlan.processProcedure(ProcedurePlan.java:356) [teiid-engine-8.4.1-redhat-7.jar:8.4.1-redhat-7]
> at org.teiid.query.processor.proc.ProcedurePlan.nextBatchDirect(ProcedurePlan.java:283) [teiid-engine-8.4.1-redhat-7.jar:8.4.1-redhat-7]
> at org.teiid.query.processor.proc.ProcedurePlan.nextBatch(ProcedurePlan.java:257) [teiid-engine-8.4.1-redhat-7.jar:8.4.1-redhat-7]
> at org.teiid.query.processor.relational.PlanExecutionNode.nextBatchDirect(PlanExecutionNode.java:118) [teiid-engine-8.4.1-redhat-7.jar:8.4.1-redhat-7]
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:278) [teiid-engine-8.4.1-redhat-7.jar:8.4.1-redhat-7]
> at org.teiid.query.processor.relational.ProjectNode.nextBatchDirect(ProjectNode.java:146) [teiid-engine-8.4.1-redhat-7.jar:8.4.1-redhat-7]
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:278) [teiid-engine-8.4.1-redhat-7.jar:8.4.1-redhat-7]
> at org.teiid.query.processor.relational.RelationalPlan.nextBatch(RelationalPlan.java:149) [teiid-engine-8.4.1-redhat-7.jar:8.4.1-redhat-7]
> at org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:149) [teiid-engine-8.4.1-redhat-7.jar:8.4.1-redhat-7]
> at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:112) [teiid-engine-8.4.1-redhat-7.jar:8.4.1-redhat-7]
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:157) [teiid-engine-8.4.1-redhat-7.jar:8.4.1-redhat-7]
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:139) [teiid-engine-8.4.1-redhat-7.jar:8.4.1-redhat-7]
> at org.teiid.dqp.internal.process.RequestWorkItem.processMore(RequestWorkItem.java:435) [teiid-engine-8.4.1-redhat-7.jar:8.4.1-redhat-7]
> at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:320) [teiid-engine-8.4.1-redhat-7.jar:8.4.1-redhat-7]
> at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:51) [teiid-engine-8.4.1-redhat-7.jar:8.4.1-redhat-7]
> at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:248) [teiid-engine-8.4.1-redhat-7.jar:8.4.1-redhat-7]
> at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:269) [teiid-engine-8.4.1-redhat-7.jar:8.4.1-redhat-7]
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119) [teiid-engine-8.4.1-redhat-7.jar:8.4.1-redhat-7]
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:214) [teiid-engine-8.4.1-redhat-7.jar:8.4.1-redhat-7]
> at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) [rt.jar:1.7.0_13]
> at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) [rt.jar:1.7.0_13]
> at java.lang.Thread.run(Thread.java:722) [rt.jar:1.7.0_13]
> Caused by: org.postgresql.util.PSQLException: ERROR: insert or update on table "history" violates foreign key constraint "history_bid_fkey"
> Detail: Key (bid)=(2625077) is not present in table "branches".
> at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2102)
> at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1835)
> at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
> at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:500)
> at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:388)
> at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:334)
> at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeUpdate(WrappedPreparedStatement.java:493)
> at org.teiid.translator.jdbc.JDBCUpdateExecution.executeTranslatedCommand(JDBCUpdateExecution.java:213)
> ... 38 more
--
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, 2 months
[JBoss JIRA] (TEIID-2458) Sybase15 subquery using HAVING clause failing
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-2458?page=com.atlassian.jira.plugin... ]
Steven Hawkins closed TEIID-2458.
---------------------------------
> Sybase15 subquery using HAVING clause failing
> ---------------------------------------------
>
> Key: TEIID-2458
> URL: https://issues.jboss.org/browse/TEIID-2458
> Project: Teiid
> Issue Type: Bug
> Components: JDBC Connector
> Affects Versions: 7.7
> Reporter: Van Halbert
> Assignee: Steven Hawkins
>
> Example query is failing: SELECT INTKEY, STRINGKEY, FLOATNUM FROM BQT1.SMALLA GROUP BY INTKEY, STRINGKEY, FLOATNUM HAVING FLOATNUM = (SELECT FLOATNUM FROM BQT1.SMALLA WHERE STRINGKEY = 20)
> When this query is submitted through EDS using Squirrel or BQT as a client it returns:
> IntKey StringKey
> 20 20 0
> When the resulting source-specific query generated from EDS is submitted direct to Sybase15 the same results are returned.
> SELECT g_0.IntKey, g_0.StringKey, cast(g_0.FloatNum AS real) FROM SmallA g_0 WHERE cast(g_0.FloatNum AS real) = (SELECT cast(g_1.FloatNum AS real) FROM SmallA g_1 WHERE g_1.StringKey = '20') GROUP BY g_0.IntKey, g_0.StringKey, cast(g_0.FloatNum AS real)is submitted direct to Sybase15
> RESULTS
> IntKey StringKey
> 20 20 0
> However, when the GROUP BY is removed from the query it returns the expected results as shown below:
> IntKey StringKey
> 20 20 -4
> The supporting VDB can be provided if needed.
--
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, 2 months