[JBoss JIRA] (TEIID-1828) Dependent critiera in query plan but missing in actual query and additional information is provided
by Steven Hawkins (Updated) (JIRA)
[ https://issues.jboss.org/browse/TEIID-1828?page=com.atlassian.jira.plugin... ]
Steven Hawkins updated TEIID-1828:
----------------------------------
Summary: Dependent critiera in query plan but missing in actual query and additional information is provided (was: Dependent critiera in query plan but missing in actual query)
Issue Type: Quality Risk (was: Bug)
Fix Version/s: 7.6
Priority: Minor (was: Major)
Assignee: Steven Hawkins (was: Van Halbert)
Workaround Description: Update relevant distinct value counts. Distinct value counts on all non-primary key join columns will allow the optimizer to determine the dependent join cost more accurately.
Workaround: Workaround Exists
In looking at enhancing our unknown ndv estimate, I'm not sure we can make many more meaningful assumptions. It would be best just to have the metadata values set. So I would recommend targeting this issue at 7.6 to add logging and additional plan information.
> Dependent critiera in query plan but missing in actual query and additional information is provided
> ---------------------------------------------------------------------------------------------------
>
> Key: TEIID-1828
> URL: https://issues.jboss.org/browse/TEIID-1828
> Project: Teiid
> Issue Type: Quality Risk
> Components: Query Engine
> Affects Versions: 7.4.1
> Environment: Oracle datasource, but likely all
> Reporter: Tom Fonteyne
> Assignee: Steven Hawkins
> Priority: Minor
> Fix For: 7.6
>
>
> tested on EDS 5.2 ER3 and ER6, same results:
> We're running a query which has two sets of dependent criteria in one of the queries. The plan looks ok, and in the command log we see that EDS is getting both sets of dependent criteria and bringing back rows which we would expect to be passed into the final query.
> The problem is that the final query has 1=1 in the place of where the dependent criteria should be.
> In the plan:
> + Query:SELECT g_0.account_code AS c_0, g_0.book_code AS c_1, SUM(g_0.func_amount) AS c_2 FROM TSL_SQL_Source.TSL.TSL_FINANCIAL_ACCOUNT_ITEMS_T2 AS g_0 WHERE (g_0.business_date = {d'2011-06-29'}) AND (g_0.account_code IN (<dependent values>)) AND (g_0.book_code IN (<dependent values>)) GROUP BY g_0.account_code, g_0.book_code ORDER BY c_0 NULLS FIRST
> The source specific query from the command log:
> 2011-11-14 11:35:35,313 DEBUG [org.teiid.CONNECTOR] (Worker1_QueryProcessorQueue60) Source-specific command: SELECT g_0.ACCOUNT_CODE AS c_0, g_0.BOOK_CODE AS c_1, SUM(g_0.FUNC_AMOUNT) AS c_2 FROM TSL_FINANCIAL_ACCOUNT_ITEMS_T2 g_0 WHERE g_0.BUSINESS_DATE = {d '2011-06-29'} AND 1 = 1 AND 1 = 1 GROUP BY g_0.ACCOUNT_CODE, g_0.BOOK_CODE ORDER BY c_0 NULLS FIRST
> Stats should be correct in the VDB. We also updated the stats using the system procedures just before executing the query.
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.jboss.org/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
14 years, 4 months
[JBoss JIRA] (TEIID-1828) Dependent critiera in query plan but missing in actual query
by Steven Hawkins (Commented) (JIRA)
[ https://issues.jboss.org/browse/TEIID-1828?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-1828:
---------------------------------------
This is expected behavior. For a cost based dependent join we also determine upper bound values after which we believe that a dependent join will be ineffective. See also https://issues.jboss.org/browse/TEIID-1533. In this particular query the relevant estimated source query cardinalities are 83 and 3408, while the actual number of distinct values from the actual executions are greater than 924 and 4687 respectively. In other words there are many more independent values than we expected.
A couple of issues then are:
The bound values are not visible in the plan. We also need to add a sql warning or a server log entry clarifying the decision to not use a dependent join.
For this case the main question is whether we made the right decision. Given the cost information we have, we believe so. However the bound may be too tight and is based upon conservative assumptions (in part based upon other side of join which is also unknown) about distinct value counts.
Additional cost information would help us make a better decision. For example if I change the ndv of account_code from the unknown value to 1/10th of the table cardinality (of course, the actual value can be different), then the back off value goes from 924 to 435523 - since we are more assured that additional independent values will return limited results.
> Dependent critiera in query plan but missing in actual query
> ------------------------------------------------------------
>
> Key: TEIID-1828
> URL: https://issues.jboss.org/browse/TEIID-1828
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 7.4.1
> Environment: Oracle datasource, but likely all
> Reporter: Tom Fonteyne
> Assignee: Van Halbert
>
> tested on EDS 5.2 ER3 and ER6, same results:
> We're running a query which has two sets of dependent criteria in one of the queries. The plan looks ok, and in the command log we see that EDS is getting both sets of dependent criteria and bringing back rows which we would expect to be passed into the final query.
> The problem is that the final query has 1=1 in the place of where the dependent criteria should be.
> In the plan:
> + Query:SELECT g_0.account_code AS c_0, g_0.book_code AS c_1, SUM(g_0.func_amount) AS c_2 FROM TSL_SQL_Source.TSL.TSL_FINANCIAL_ACCOUNT_ITEMS_T2 AS g_0 WHERE (g_0.business_date = {d'2011-06-29'}) AND (g_0.account_code IN (<dependent values>)) AND (g_0.book_code IN (<dependent values>)) GROUP BY g_0.account_code, g_0.book_code ORDER BY c_0 NULLS FIRST
> The source specific query from the command log:
> 2011-11-14 11:35:35,313 DEBUG [org.teiid.CONNECTOR] (Worker1_QueryProcessorQueue60) Source-specific command: SELECT g_0.ACCOUNT_CODE AS c_0, g_0.BOOK_CODE AS c_1, SUM(g_0.FUNC_AMOUNT) AS c_2 FROM TSL_FINANCIAL_ACCOUNT_ITEMS_T2 g_0 WHERE g_0.BUSINESS_DATE = {d '2011-06-29'} AND 1 = 1 AND 1 = 1 GROUP BY g_0.ACCOUNT_CODE, g_0.BOOK_CODE ORDER BY c_0 NULLS FIRST
> Stats should be correct in the VDB. We also updated the stats using the system procedures just before executing the query.
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.jboss.org/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
14 years, 4 months
[JBoss JIRA] (TEIID-1828) Dependent critiera in query plan but missing in actual query
by Steven Hawkins (Updated) (JIRA)
[ https://issues.jboss.org/browse/TEIID-1828?page=com.atlassian.jira.plugin... ]
Steven Hawkins updated TEIID-1828:
----------------------------------
Attachment: (was: TSL_POC.vdb)
> Dependent critiera in query plan but missing in actual query
> ------------------------------------------------------------
>
> Key: TEIID-1828
> URL: https://issues.jboss.org/browse/TEIID-1828
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 7.4.1
> Environment: Oracle datasource, but likely all
> Reporter: Tom Fonteyne
> Assignee: Van Halbert
>
> tested on EDS 5.2 ER3 and ER6, same results:
> We're running a query which has two sets of dependent criteria in one of the queries. The plan looks ok, and in the command log we see that EDS is getting both sets of dependent criteria and bringing back rows which we would expect to be passed into the final query.
> The problem is that the final query has 1=1 in the place of where the dependent criteria should be.
> In the plan:
> + Query:SELECT g_0.account_code AS c_0, g_0.book_code AS c_1, SUM(g_0.func_amount) AS c_2 FROM TSL_SQL_Source.TSL.TSL_FINANCIAL_ACCOUNT_ITEMS_T2 AS g_0 WHERE (g_0.business_date = {d'2011-06-29'}) AND (g_0.account_code IN (<dependent values>)) AND (g_0.book_code IN (<dependent values>)) GROUP BY g_0.account_code, g_0.book_code ORDER BY c_0 NULLS FIRST
> The source specific query from the command log:
> 2011-11-14 11:35:35,313 DEBUG [org.teiid.CONNECTOR] (Worker1_QueryProcessorQueue60) Source-specific command: SELECT g_0.ACCOUNT_CODE AS c_0, g_0.BOOK_CODE AS c_1, SUM(g_0.FUNC_AMOUNT) AS c_2 FROM TSL_FINANCIAL_ACCOUNT_ITEMS_T2 g_0 WHERE g_0.BUSINESS_DATE = {d '2011-06-29'} AND 1 = 1 AND 1 = 1 GROUP BY g_0.ACCOUNT_CODE, g_0.BOOK_CODE ORDER BY c_0 NULLS FIRST
> Stats should be correct in the VDB. We also updated the stats using the system procedures just before executing the query.
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.jboss.org/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
14 years, 4 months
[JBoss JIRA] (TEIID-1826) RFE/possible optimization: push down parts of some aggregate expressions
by Steven Hawkins (Resolved) (JIRA)
[ https://issues.jboss.org/browse/TEIID-1826?page=com.atlassian.jira.plugin... ]
Steven Hawkins resolved TEIID-1826.
-----------------------------------
Assignee: Steven Hawkins (was: Van Halbert)
Resolution: Deferred
Thinking about this more, we should just mark as resolved. Unless there is a specific need otherwise, the resolution here is for the user to use single source aggregate expressions.
> RFE/possible optimization: push down parts of some aggregate expressions
> ------------------------------------------------------------------------
>
> Key: TEIID-1826
> URL: https://issues.jboss.org/browse/TEIID-1826
> Project: Teiid
> Issue Type: Feature Request
> Components: Query Engine
> Reporter: Debbie Steigner
> Assignee: Steven Hawkins
> Priority: Optional
>
> suggestion for a potential optimization: It concerns federated queries which do expressions. This is an extremely common requirement in the banking business, the main example being when monetary amounts need to be converted into a reporting currency using a rate in another database.
> With the query below, it makes sense that despite the group by, EDS will pull back all the rows so it can multiply one by the other. Unfortunately this is often many millions of records.
> Would it be feasible to have an optimization which push-down most of the aggregation to the source, then do a further aggregation in EDS?
> * sum the tsl.func_amount, grouped by tsl.currency
> * next: get the conversion rates for all the currency codes which have been returned, then sum(total * rate)
> Of course, this can only be done for certain kinds of expressions.
> select sum(tsl.func_amount * ccy.EuroConversionLegacyRate)
> from TSL_FINANCIAL_ACCOUNT_ITEMS_T2 tsl
> join t_sdm_currency ccy on tsl.currency = ccy.currencycode
> where tsl.business_date = '2011-06-29'
> group by ccy.EuroConversionLegacyRate
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.jboss.org/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
14 years, 4 months
[JBoss JIRA] (TEIID-1828) Dependent critiera in query plan but missing in actual query
by Steven Hawkins (Updated) (JIRA)
[ https://issues.jboss.org/browse/TEIID-1828?page=com.atlassian.jira.plugin... ]
Steven Hawkins updated TEIID-1828:
----------------------------------
Attachment: (was: dal-command.log)
> Dependent critiera in query plan but missing in actual query
> ------------------------------------------------------------
>
> Key: TEIID-1828
> URL: https://issues.jboss.org/browse/TEIID-1828
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 7.4.1
> Environment: Oracle datasource, but likely all
> Reporter: Tom Fonteyne
> Assignee: Van Halbert
>
> tested on EDS 5.2 ER3 and ER6, same results:
> We're running a query which has two sets of dependent criteria in one of the queries. The plan looks ok, and in the command log we see that EDS is getting both sets of dependent criteria and bringing back rows which we would expect to be passed into the final query.
> The problem is that the final query has 1=1 in the place of where the dependent criteria should be.
> In the plan:
> + Query:SELECT g_0.account_code AS c_0, g_0.book_code AS c_1, SUM(g_0.func_amount) AS c_2 FROM TSL_SQL_Source.TSL.TSL_FINANCIAL_ACCOUNT_ITEMS_T2 AS g_0 WHERE (g_0.business_date = {d'2011-06-29'}) AND (g_0.account_code IN (<dependent values>)) AND (g_0.book_code IN (<dependent values>)) GROUP BY g_0.account_code, g_0.book_code ORDER BY c_0 NULLS FIRST
> The source specific query from the command log:
> 2011-11-14 11:35:35,313 DEBUG [org.teiid.CONNECTOR] (Worker1_QueryProcessorQueue60) Source-specific command: SELECT g_0.ACCOUNT_CODE AS c_0, g_0.BOOK_CODE AS c_1, SUM(g_0.FUNC_AMOUNT) AS c_2 FROM TSL_FINANCIAL_ACCOUNT_ITEMS_T2 g_0 WHERE g_0.BUSINESS_DATE = {d '2011-06-29'} AND 1 = 1 AND 1 = 1 GROUP BY g_0.ACCOUNT_CODE, g_0.BOOK_CODE ORDER BY c_0 NULLS FIRST
> Stats should be correct in the VDB. We also updated the stats using the system procedures just before executing the query.
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.jboss.org/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
14 years, 4 months
[JBoss JIRA] (TEIID-1828) Dependent critiera in query plan but missing in actual query
by Steven Hawkins (Updated) (JIRA)
[ https://issues.jboss.org/browse/TEIID-1828?page=com.atlassian.jira.plugin... ]
Steven Hawkins updated TEIID-1828:
----------------------------------
Attachment: (was: server.log)
> Dependent critiera in query plan but missing in actual query
> ------------------------------------------------------------
>
> Key: TEIID-1828
> URL: https://issues.jboss.org/browse/TEIID-1828
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 7.4.1
> Environment: Oracle datasource, but likely all
> Reporter: Tom Fonteyne
> Assignee: Van Halbert
>
> tested on EDS 5.2 ER3 and ER6, same results:
> We're running a query which has two sets of dependent criteria in one of the queries. The plan looks ok, and in the command log we see that EDS is getting both sets of dependent criteria and bringing back rows which we would expect to be passed into the final query.
> The problem is that the final query has 1=1 in the place of where the dependent criteria should be.
> In the plan:
> + Query:SELECT g_0.account_code AS c_0, g_0.book_code AS c_1, SUM(g_0.func_amount) AS c_2 FROM TSL_SQL_Source.TSL.TSL_FINANCIAL_ACCOUNT_ITEMS_T2 AS g_0 WHERE (g_0.business_date = {d'2011-06-29'}) AND (g_0.account_code IN (<dependent values>)) AND (g_0.book_code IN (<dependent values>)) GROUP BY g_0.account_code, g_0.book_code ORDER BY c_0 NULLS FIRST
> The source specific query from the command log:
> 2011-11-14 11:35:35,313 DEBUG [org.teiid.CONNECTOR] (Worker1_QueryProcessorQueue60) Source-specific command: SELECT g_0.ACCOUNT_CODE AS c_0, g_0.BOOK_CODE AS c_1, SUM(g_0.FUNC_AMOUNT) AS c_2 FROM TSL_FINANCIAL_ACCOUNT_ITEMS_T2 g_0 WHERE g_0.BUSINESS_DATE = {d '2011-06-29'} AND 1 = 1 AND 1 = 1 GROUP BY g_0.ACCOUNT_CODE, g_0.BOOK_CODE ORDER BY c_0 NULLS FIRST
> Stats should be correct in the VDB. We also updated the stats using the system procedures just before executing the query.
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.jboss.org/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
14 years, 4 months
[JBoss JIRA] (TEIID-1828) Dependent critiera in query plan but missing in actual query
by Steven Hawkins (Updated) (JIRA)
[ https://issues.jboss.org/browse/TEIID-1828?page=com.atlassian.jira.plugin... ]
Steven Hawkins updated TEIID-1828:
----------------------------------
Attachment: (was: no_dep_criteria_plan.log)
> Dependent critiera in query plan but missing in actual query
> ------------------------------------------------------------
>
> Key: TEIID-1828
> URL: https://issues.jboss.org/browse/TEIID-1828
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 7.4.1
> Environment: Oracle datasource, but likely all
> Reporter: Tom Fonteyne
> Assignee: Van Halbert
>
> tested on EDS 5.2 ER3 and ER6, same results:
> We're running a query which has two sets of dependent criteria in one of the queries. The plan looks ok, and in the command log we see that EDS is getting both sets of dependent criteria and bringing back rows which we would expect to be passed into the final query.
> The problem is that the final query has 1=1 in the place of where the dependent criteria should be.
> In the plan:
> + Query:SELECT g_0.account_code AS c_0, g_0.book_code AS c_1, SUM(g_0.func_amount) AS c_2 FROM TSL_SQL_Source.TSL.TSL_FINANCIAL_ACCOUNT_ITEMS_T2 AS g_0 WHERE (g_0.business_date = {d'2011-06-29'}) AND (g_0.account_code IN (<dependent values>)) AND (g_0.book_code IN (<dependent values>)) GROUP BY g_0.account_code, g_0.book_code ORDER BY c_0 NULLS FIRST
> The source specific query from the command log:
> 2011-11-14 11:35:35,313 DEBUG [org.teiid.CONNECTOR] (Worker1_QueryProcessorQueue60) Source-specific command: SELECT g_0.ACCOUNT_CODE AS c_0, g_0.BOOK_CODE AS c_1, SUM(g_0.FUNC_AMOUNT) AS c_2 FROM TSL_FINANCIAL_ACCOUNT_ITEMS_T2 g_0 WHERE g_0.BUSINESS_DATE = {d '2011-06-29'} AND 1 = 1 AND 1 = 1 GROUP BY g_0.ACCOUNT_CODE, g_0.BOOK_CODE ORDER BY c_0 NULLS FIRST
> Stats should be correct in the VDB. We also updated the stats using the system procedures just before executing the query.
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.jboss.org/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
14 years, 4 months
[JBoss JIRA] (TEIID-1828) Dependent critiera in query plan but missing in actual query
by Van Halbert (Moved) (JIRA)
[ https://issues.jboss.org/browse/TEIID-1828?page=com.atlassian.jira.plugin... ]
Van Halbert moved SOA-3586 to TEIID-1828:
-----------------------------------------
Project: Teiid (was: JBoss Enterprise SOA Platform)
Key: TEIID-1828 (was: SOA-3586)
Affects Version/s: 7.4.1
(was: 5.2.0.ER6)
Component/s: Query Engine
(was: EDS)
Security: (was: Public)
Fix Version/s: (was: 5.2.0 GA)
> Dependent critiera in query plan but missing in actual query
> ------------------------------------------------------------
>
> Key: TEIID-1828
> URL: https://issues.jboss.org/browse/TEIID-1828
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 7.4.1
> Environment: Oracle datasource, but likely all
> Reporter: Tom Fonteyne
> Assignee: Van Halbert
> Attachments: dal-command.log, no_dep_criteria_plan.log, server.log, TSL_POC.vdb
>
>
> tested on EDS 5.2 ER3 and ER6, same results:
> We're running a query which has two sets of dependent criteria in one of the queries. The plan looks ok, and in the command log we see that EDS is getting both sets of dependent criteria and bringing back rows which we would expect to be passed into the final query.
> The problem is that the final query has 1=1 in the place of where the dependent criteria should be.
> In the plan:
> + Query:SELECT g_0.account_code AS c_0, g_0.book_code AS c_1, SUM(g_0.func_amount) AS c_2 FROM TSL_SQL_Source.TSL.TSL_FINANCIAL_ACCOUNT_ITEMS_T2 AS g_0 WHERE (g_0.business_date = {d'2011-06-29'}) AND (g_0.account_code IN (<dependent values>)) AND (g_0.book_code IN (<dependent values>)) GROUP BY g_0.account_code, g_0.book_code ORDER BY c_0 NULLS FIRST
> The source specific query from the command log:
> 2011-11-14 11:35:35,313 DEBUG [org.teiid.CONNECTOR] (Worker1_QueryProcessorQueue60) Source-specific command: SELECT g_0.ACCOUNT_CODE AS c_0, g_0.BOOK_CODE AS c_1, SUM(g_0.FUNC_AMOUNT) AS c_2 FROM TSL_FINANCIAL_ACCOUNT_ITEMS_T2 g_0 WHERE g_0.BUSINESS_DATE = {d '2011-06-29'} AND 1 = 1 AND 1 = 1 GROUP BY g_0.ACCOUNT_CODE, g_0.BOOK_CODE ORDER BY c_0 NULLS FIRST
> Stats should be correct in the VDB. We also updated the stats using the system procedures just before executing the query.
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.jboss.org/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
14 years, 4 months
[JBoss JIRA] (TEIID-1826) RFE/possible optimization: push down parts of some aggregate expressions
by Steven Hawkins (Commented) (JIRA)
[ https://issues.jboss.org/browse/TEIID-1826?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-1826:
---------------------------------------
Partial aggregate pushdown is already implemented, but it is limited to aggregate expressions against a single join source. If the query above were written as the product of sums, the partial pushdown would occur - e.g.
select sum(tsl.func_amount) * sum(ccy.EuroConversionLegacyRate) ...
Given the simplicity of the workaround, and the unbounded complexity of potential aggregate expressions, this would be a minor priority at best and should probably just be resolved as deferred.
> RFE/possible optimization: push down parts of some aggregate expressions
> ------------------------------------------------------------------------
>
> Key: TEIID-1826
> URL: https://issues.jboss.org/browse/TEIID-1826
> Project: Teiid
> Issue Type: Feature Request
> Components: Query Engine
> Reporter: Debbie Steigner
> Assignee: Van Halbert
>
> suggestion for a potential optimization: It concerns federated queries which do expressions. This is an extremely common requirement in the banking business, the main example being when monetary amounts need to be converted into a reporting currency using a rate in another database.
> With the query below, it makes sense that despite the group by, EDS will pull back all the rows so it can multiply one by the other. Unfortunately this is often many millions of records.
> Would it be feasible to have an optimization which push-down most of the aggregation to the source, then do a further aggregation in EDS?
> * sum the tsl.func_amount, grouped by tsl.currency
> * next: get the conversion rates for all the currency codes which have been returned, then sum(total * rate)
> Of course, this can only be done for certain kinds of expressions.
> select sum(tsl.func_amount * ccy.EuroConversionLegacyRate)
> from TSL_FINANCIAL_ACCOUNT_ITEMS_T2 tsl
> join t_sdm_currency ccy on tsl.currency = ccy.currencycode
> where tsl.business_date = '2011-06-29'
> group by ccy.EuroConversionLegacyRate
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.jboss.org/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
14 years, 4 months
[JBoss JIRA] (TEIID-1826) RFE/possible optimization: push down parts of some aggregate expressions
by Steven Hawkins (Updated) (JIRA)
[ https://issues.jboss.org/browse/TEIID-1826?page=com.atlassian.jira.plugin... ]
Steven Hawkins updated TEIID-1826:
----------------------------------
Priority: Optional (was: Major)
> RFE/possible optimization: push down parts of some aggregate expressions
> ------------------------------------------------------------------------
>
> Key: TEIID-1826
> URL: https://issues.jboss.org/browse/TEIID-1826
> Project: Teiid
> Issue Type: Feature Request
> Components: Query Engine
> Reporter: Debbie Steigner
> Assignee: Van Halbert
> Priority: Optional
>
> suggestion for a potential optimization: It concerns federated queries which do expressions. This is an extremely common requirement in the banking business, the main example being when monetary amounts need to be converted into a reporting currency using a rate in another database.
> With the query below, it makes sense that despite the group by, EDS will pull back all the rows so it can multiply one by the other. Unfortunately this is often many millions of records.
> Would it be feasible to have an optimization which push-down most of the aggregation to the source, then do a further aggregation in EDS?
> * sum the tsl.func_amount, grouped by tsl.currency
> * next: get the conversion rates for all the currency codes which have been returned, then sum(total * rate)
> Of course, this can only be done for certain kinds of expressions.
> select sum(tsl.func_amount * ccy.EuroConversionLegacyRate)
> from TSL_FINANCIAL_ACCOUNT_ITEMS_T2 tsl
> join t_sdm_currency ccy on tsl.currency = ccy.currencycode
> where tsl.business_date = '2011-06-29'
> group by ccy.EuroConversionLegacyRate
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.jboss.org/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
14 years, 4 months