[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:
---------------------------------------
So the full comparison to make is a JDBC query with a scroll insensitive resultset vs. the OData query. You can get a rough idea of the OData overhead if you repeat this process for various data widths. I am not certain how much overhead to expect.
You can also compare a forward only JDBC query to scroll insensitive query. This will give an estimate of the overhead of the more proactive processing associated with a scrolling result - which could be quite significant for larger results.
>From what I can see there isn't a good reason to use scroll insensitive when we aren't obtaining the row count, so that would be a likely change. 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-2892) OData buffers ALL rows from resultset before returning the first batch
by Patrick Deenen (JIRA)
[ https://issues.jboss.org/browse/TEIID-2892?page=com.atlassian.jira.plugin... ]
Patrick Deenen commented on TEIID-2892:
---------------------------------------
Odata is 4 times slower than JDBC for this query.
> 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-2906) bind variables and sql appear when exception thrown
by RH Bugzilla Integration (JIRA)
[ https://issues.jboss.org/browse/TEIID-2906?page=com.atlassian.jira.plugin... ]
RH Bugzilla Integration updated TEIID-2906:
-------------------------------------------
Bugzilla Update: Perform
Bugzilla References: https://bugzilla.redhat.com/show_bug.cgi?id=1090173
> bind variables and sql appear when exception thrown
> ---------------------------------------------------
>
> Key: TEIID-2906
> URL: https://issues.jboss.org/browse/TEIID-2906
> Project: Teiid
> Issue Type: Bug
> Components: JDBC Connector
> Affects Versions: 8.4
> Environment: - JDV 6.0.0
> Reporter: Hisanobu Okuda
> Assignee: Steven Hawkins
> Fix For: 8.7
>
> Attachments: PreparedStatementTest.java, test.vdb
>
>
> When a query causes an exception, bind variables and sql are written in teiid-command.log and are sent to a client.
> teiid-command.log:-
> {code}
> 19:36:19,942 WARN [org.teiid.PROCESSOR] (Worker8_QueryProcessorQueue29) TEIID30020 Processing exception for request 6jFRdyvDG5bU.0
> 'TEIID30504 New_MySQL: 0 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: ['hokuda'] SQL: SELECT g_0.`USERNAM
> E`, g_0.`PASSWORD`, g_0.`USERROLE` FROM `LOGIN`.`USERS` AS g_0 WHERE g_0.`USERNAME` = ?]'. Originally TeiidProcessingException 'Can
> not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost.' MysqlIO.java:3039.
> Enable more detailed logging to see the entire stacktrace.
> {code}
> printStackTrace() at client side:-
> {code}
> org.teiid.jdbc.TeiidSQLException: TEIID30504 Remote org.teiid.core.TeiidProcessingException: TEIID30504 New_MySQL: 0 TEIID11008:TEI
> ID11004 Error executing statement(s): [Prepared Values: ['hokuda'] SQL: SELECT g_0.`USERNAME`, g_0.`PASSWORD`, g_0.`USERROLE` FROM
> `LOGIN`.`USERS` AS g_0 WHERE g_0.`USERNAME` = ?]
> at org.teiid.jdbc.TeiidSQLException.create(TeiidSQLException.java:135)
> at org.teiid.jdbc.TeiidSQLException.create(TeiidSQLException.java:71)
> {code}
--
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-2699) virtual procedure with IN params get errors
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-2699?page=com.atlassian.jira.plugin... ]
Steven Hawkins closed TEIID-2699.
---------------------------------
> virtual procedure with IN params get errors
> -------------------------------------------
>
> Key: TEIID-2699
> URL: https://issues.jboss.org/browse/TEIID-2699
> Project: Teiid
> Issue Type: Bug
> Components: XML Planner/Processor
> Affects Versions: 8.3
> Environment: Windows XP, java 1.6.0_04
> Reporter: luca gioppo
> Assignee: Steven Hawkins
>
> I've creted a virtual procedure like this one:
> CREATE VIRTUAL PROCEDURE
> BEGIN
> IF(accident_proc.getaccident.id = null)
> BEGIN
> SELECT XMLELEMENT(NAME accidents, XMLAGG(XMLELEMENT(NAME accident, XMLFOREST(accident_view.accident.id, accident_view.accident.lat, accident_view.accident.lon, accident_view.accident.accident_date)))) AS result FROM accident_view.accident;
> END
> ELSE
> BEGIN
> SELECT XMLELEMENT(NAME accidents, XMLAGG(XMLELEMENT(NAME accident, XMLFOREST(accident_view.accident.id, accident_view.accident.lat, accident_view.accident.lon, accident_view.accident.accident_date)))) AS result FROM accident_view.accident WHERE accident_view.accident.id = accident_proc.getaccident.id;
> END
> END
> invoke it from squirrelsql like this
> select * from accident_proc.getaccident where id=1;
> the actual table has just 1 row that is exposed through the VDB and can be red correctly.
> I get this exception
> 14:56:42,573 ERROR [org.teiid.PROCESSOR] (Worker7_QueryProcessorQueue28) TEIID30019 Unexpected exception for request OFF
> fN2xoRDPn.8: java.lang.NoSuchMethodError: javax.xml.stream.XMLEventFactory.newFactory()Ljavax/xml/stream/XMLEventFactory
> ;
> at org.teiid.query.function.source.XMLSystemFunctions$4.initialValue(XMLSystemFunctions.java:395) [teiid-engine-
> 8.3.0.Beta2.jar:8.3.0.Beta2]
> at org.teiid.query.function.source.XMLSystemFunctions$4.initialValue(XMLSystemFunctions.java:393) [teiid-engine-
> 8.3.0.Beta2.jar:8.3.0.Beta2]
> at java.lang.ThreadLocal.setInitialValue(ThreadLocal.java:141) [rt.jar:1.6.0_04]
> at java.lang.ThreadLocal.get(ThreadLocal.java:131) [rt.jar:1.6.0_04]
> at org.teiid.query.function.source.XMLSystemFunctions$4.get(XMLSystemFunctions.java:398) [teiid-engine-8.3.0.Bet
> a2.jar:8.3.0.Beta2]
> at org.teiid.query.function.source.XMLSystemFunctions$4.get(XMLSystemFunctions.java:393) [teiid-engine-8.3.0.Bet
> a2.jar:8.3.0.Beta2]
> at org.teiid.query.function.source.XMLSystemFunctions$6.translate(XMLSystemFunctions.java:476) [teiid-engine-8.3
> .0.Beta2.jar:8.3.0.Beta2]
> at org.teiid.query.function.source.XMLSystemFunctions.saveToBufferManager(XMLSystemFunctions.java:989) [teiid-en
> gine-8.3.0.Beta2.jar:8.3.0.Beta2]
> at org.teiid.query.function.source.XMLSystemFunctions.xmlForest(XMLSystemFunctions.java:468) [teiid-engine-8.3.0
> .Beta2.jar:8.3.0.Beta2]
> at org.teiid.query.eval.Evaluator.evaluateXMLForest(Evaluator.java:887) [teiid-engine-8.3.0.Beta2.jar:8.3.0.Beta
> 2]
> at org.teiid.query.eval.Evaluator.internalEvaluate(Evaluator.java:648) [teiid-engine-8.3.0.Beta2.jar:8.3.0.Beta2
> ]
> at org.teiid.query.eval.Evaluator.evaluateXMLElement(Evaluator.java:915) [teiid-engine-8.3.0.Beta2.jar:8.3.0.Bet
> a2]
> at org.teiid.query.eval.Evaluator.internalEvaluate(Evaluator.java:646) [teiid-engine-8.3.0.Beta2.jar:8.3.0.Beta2
> ]
> at org.teiid.query.eval.Evaluator.evaluate(Evaluator.java:596) [teiid-engine-8.3.0.Beta2.jar:8.3.0.Beta2]
> at org.teiid.query.processor.relational.GroupingNode$ProjectingTupleSource.updateTuple(GroupingNode.java:84) [te
> iid-engine-8.3.0.Beta2.jar:8.3.0.Beta2]
> at org.teiid.query.processor.BatchCollector$BatchProducerTupleSource.nextTuple(BatchCollector.java:95) [teiid-en
> gine-8.3.0.Beta2.jar:8.3.0.Beta2]
> at org.teiid.query.processor.relational.GroupingNode.groupPhase(GroupingNode.java:382) [teiid-engine-8.3.0.Beta2
> .jar:8.3.0.Beta2]
> at org.teiid.query.processor.relational.GroupingNode.nextBatchDirect(GroupingNode.java:322) [teiid-engine-8.3.0.
> Beta2.jar:8.3.0.Beta2]
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:279) [teiid-engine-8.3.0.Be
> ta2.jar:8.3.0.Beta2]
> at org.teiid.query.processor.relational.ProjectNode.nextBatchDirect(ProjectNode.java:146) [teiid-engine-8.3.0.Be
> ta2.jar:8.3.0.Beta2]
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:279) [teiid-engine-8.3.0.Be
> ta2.jar:8.3.0.Beta2]
> at org.teiid.query.processor.relational.RelationalPlan.nextBatch(RelationalPlan.java:149) [teiid-engine-8.3.0.Be
> ta2.jar:8.3.0.Beta2]
> at org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:144) [teiid-engine-8.3.0.Beta2.j
> ar:8.3.0.Beta2]
> at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:110) [teiid-engine-8.3.0.Beta2.jar:8.3
> .0.Beta2]
> at org.teiid.query.processor.BatchIterator.finalRow(BatchIterator.java:70) [teiid-engine-8.3.0.Beta2.jar:8.3.0.B
> eta2]
> at org.teiid.common.buffer.AbstractTupleSource.getCurrentTuple(AbstractTupleSource.java:69) [teiid-engine-8.3.0.
> Beta2.jar:8.3.0.Beta2]
> at org.teiid.query.processor.BatchIterator.getCurrentTuple(BatchIterator.java:85) [teiid-engine-8.3.0.Beta2.jar:
> 8.3.0.Beta2]
> at org.teiid.common.buffer.AbstractTupleSource.hasNext(AbstractTupleSource.java:91) [teiid-engine-8.3.0.Beta2.ja
> r:8.3.0.Beta2]
> at org.teiid.query.processor.proc.ProcedurePlan.executePlan(ProcedurePlan.java:579) [teiid-engine-8.3.0.Beta2.ja
> r:8.3.0.Beta2]
> at org.teiid.query.processor.proc.CreateCursorResultSetInstruction.process(CreateCursorResultSetInstruction.java
> :68) [teiid-engine-8.3.0.Beta2.jar:8.3.0.Beta2]
> at org.teiid.query.processor.proc.ProcedurePlan.processProcedure(ProcedurePlan.java:388) [teiid-engine-8.3.0.Bet
> a2.jar:8.3.0.Beta2]
> at org.teiid.query.processor.proc.ProcedurePlan.nextBatchDirect(ProcedurePlan.java:315) [teiid-engine-8.3.0.Beta
> 2.jar:8.3.0.Beta2]
> at org.teiid.query.processor.proc.ProcedurePlan.nextBatch(ProcedurePlan.java:289) [teiid-engine-8.3.0.Beta2.jar:
> 8.3.0.Beta2]
> at org.teiid.query.processor.relational.PlanExecutionNode.nextBatchDirect(PlanExecutionNode.java:118) [teiid-eng
> ine-8.3.0.Beta2.jar:8.3.0.Beta2]
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:279) [teiid-engine-8.3.0.Be
> ta2.jar:8.3.0.Beta2]
> at org.teiid.query.processor.relational.ProjectNode.nextBatchDirect(ProjectNode.java:146) [teiid-engine-8.3.0.Be
> ta2.jar:8.3.0.Beta2]
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:279) [teiid-engine-8.3.0.Be
> ta2.jar:8.3.0.Beta2]
> at org.teiid.query.processor.relational.ProjectNode.nextBatchDirect(ProjectNode.java:146) [teiid-engine-8.3.0.Be
> ta2.jar:8.3.0.Beta2]
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:279) [teiid-engine-8.3.0.Be
> ta2.jar:8.3.0.Beta2]
> at org.teiid.query.processor.relational.LimitNode.nextBatchDirect(LimitNode.java:101) [teiid-engine-8.3.0.Beta2.
> jar:8.3.0.Beta2]
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:279) [teiid-engine-8.3.0.Be
> ta2.jar:8.3.0.Beta2]
> at org.teiid.query.processor.relational.ProjectNode.nextBatchDirect(ProjectNode.java:146) [teiid-engine-8.3.0.Be
> ta2.jar:8.3.0.Beta2]
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:279) [teiid-engine-8.3.0.Be
> ta2.jar:8.3.0.Beta2]
> at org.teiid.query.processor.relational.RelationalPlan.nextBatch(RelationalPlan.java:149) [teiid-engine-8.3.0.Be
> ta2.jar:8.3.0.Beta2]
> at org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:144) [teiid-engine-8.3.0.Beta2.j
> ar:8.3.0.Beta2]
> at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:110) [teiid-engine-8.3.0.Beta2.jar:8.3
> .0.Beta2]
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:153) [teiid-engine-8.3.0.Beta2.jar
> :8.3.0.Beta2]
> at org.teiid.dqp.internal.process.RequestWorkItem.processMore(RequestWorkItem.java:400) [teiid-engine-8.3.0.Beta
> 2.jar:8.3.0.Beta2]
> at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:296) [teiid-engine-8.3.0.Beta2.ja
> r:8.3.0.Beta2]
> at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:49) [teiid-engine-8.3.0.Beta2.jar:8
> .3.0.Beta2]
> at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:224) [teiid-engine-8.3.0.Beta2.jar:8.
> 3.0.Beta2]
> at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:253) [teiid-engine-8.3.0.Beta2
> .jar:8.3.0.Beta2]
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:123) [teiid-e
> ngine-8.3.0.Beta2.jar:8.3.0.Beta2]
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:298) [teiid-engine-8.3.0.Be
> ta2.jar:8.3.0.Beta2]
> at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:885) [rt.jar:1.6.0_04]
> at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:907) [rt.jar:1.6.0_04]
> at java.lang.Thread.run(Thread.java:619) [rt.jar:1.6.0_04]
--
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-2555) Support pushdown of entire dependent joins
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-2555?page=com.atlassian.jira.plugin... ]
Steven Hawkins closed TEIID-2555.
---------------------------------
> Support pushdown of entire dependent joins
> ------------------------------------------
>
> Key: TEIID-2555
> URL: https://issues.jboss.org/browse/TEIID-2555
> Project: Teiid
> Issue Type: Sub-task
> Components: Connector API, Query Engine
> Reporter: Steven Hawkins
> Assignee: Steven Hawkins
> Fix For: 8.5
>
>
> If the data volume is not too large then in many circumstances pushing down the entire independent side of the join to perform the entire join at the source can enhance performance.
> This would likely be built upon TEIID-2249 to make use of a make dep hint option. It would also likely be an expansion of the common table pushdown logic - but will require more extensive planning changes as the default logic is geared toward only the equi-join columns.
> It has also been requested that the default preference for pushdown be based upon the estimated data width.
> There is an issue with the form of the plan as with the existing logic it would be nearly impossible to back out of the decision to perform the full pushdown (which is why a hint is initially preferable).
--
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-2311) Add simple row based security to data roles
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-2311?page=com.atlassian.jira.plugin... ]
Steven Hawkins closed TEIID-2311.
---------------------------------
> Add simple row based security to data roles
> -------------------------------------------
>
> Key: TEIID-2311
> URL: https://issues.jboss.org/browse/TEIID-2311
> Project: Teiid
> Issue Type: Feature Request
> Components: Query Engine
> Affects Versions: 8.2
> Reporter: Steven Hawkins
> Assignee: Steven Hawkins
> Fix For: 8.3
>
>
> A common request is to implement row based security. The common workaround of modifying transformations is generally not a good solution.
> We should look at adding support for simple table filters and column masks.
> To be effective, filtering permissions however would have to act differently than normal data roles. They would need to be applied all the time - and not just against the end user queries.
> For example, for tables:
> <permission>
> <resource-name>SCHEMA.TABLE</resource-name>
> <filter>COLUMNA=2</filter>
> </permission>
> Meaning allow the CRUD of the given row only if COLUMNA has the value of 2. Any valid predicate against just the referenced table would be allowed as a filter. Each such permission would be applied as an additional predicate any time the table is referenced (in views, inserts, updates, deletes, etc.).
> Allows would not be specified here as we want the filter to always specify inclusion. Any applicable permissions in additional roles would be applied disjunctively - filter OR filter.
> We could possibly support column masks via case expressions, such as:
> <permission>
> <resource-name>SCHEMA.TABLE.COLUMN</resource-name>
> <mask>CASE WHEN ...</mask>
> </permission>
> However this is slightly more complicated. Presumably the mask would only apply to projection and makes more sense to be applied at the final output/user query (more like a data role).
> If we work the issue to specify the object type of a permission, then the name could alternatively refer to datatype or even an extension property to make the masking a little easier.
--
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