Uploaded image for project: 'Teiid'
  1. Teiid
  2. TEIID-1826

RFE/possible optimization: push down parts of some aggregate expressions

    XMLWordPrintable

    Details

    • Type: Feature Request
    • Status: Closed (View Workflow)
    • Priority: Optional
    • Resolution: Deferred
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: Query Engine
    • Labels:
      None

      Description

      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

        Gliffy Diagrams

          Attachments

            Issue Links

              Activity

                People

                • Assignee:
                  shawkins Steven Hawkins
                  Reporter:
                  dsteigne Deborah Steigner
                • Votes:
                  0 Vote for this issue
                  Watchers:
                  1 Start watching this issue

                  Dates

                  • Created:
                    Updated:
                    Resolved: