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

[Steven Hawkins (Closed) (JIRA)]

     [ https://issues.jboss.org/browse/TEIID-1826?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Steven Hawkins closed TEIID-1826.
---------------------------------


    
> 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