2 Replies Latest reply on Feb 12, 2015 4:22 PM by Steven Hawkins

    Joins with multiple queries to dependent data source

    Sameer Babu KK Newbie

      Hi,

       

      I have a query joining tables in two data sources like:

       

      select a.key, a.value1, a.date, a.value2, b.tag1, a.value2 from a left outer join b on a.key = b.key where a.date = 'time' and a.value1 = 'some value' order by a.value2.

       

      In this case TEIID generates queries like:

       

      1. select a.key, a.value1, a.date, a.value2, b.tag1, a.value2 from a where a.date = 'time' and a.value1 = 'some value' order by a.key.

      2. select b.key, b.tag1 from b where b.key in (keys from first query)

       

      then does the order with a.value2

       

      so if I have lots of records in table a it consumes large amount of memory and processing, since it needs to keep all records in memory, join and then sort.

       

      My question is about how to push down order on a.value2 instead of a.key and also return the results faster. Can TEIID send multiple queries to b to get the b.tag1 (and cache tag1 for the key) as and when the results are available from a? This can return the results much faster. Also in the same scenario I have a count query like below.

       

      select count(*) from a left outer join b on a.key = b.key where a.date = 'time' and a.value1 = 'some value'.

       

      In this case also count is not push down. I have a specified in the translator that it supports countStar and count.

       

      Best Regards,

      Sameer