[teiid-issues] [JBoss JIRA] (TEIID-1826) RFE/possible optimization: push down parts of some aggregate expressions

[Steven Hawkins (Commented) (JIRA)]

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

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