Joins with multiple queries to dependent data source

[sameerbkk]

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

[rareddy]

Sammer,

You should look into a optimization called "data-ship" join that can create a temp table with a.value2 and avoid the IN clause. See here Federated Optimizations - Teiid 8.10 (draft) - Project Documentation Editor look under "Full Pushdown"

Ramesh..

[shawkins]

> 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.

Just to clarify we don't hold everything in memory.  It's paged to disk as needed by the buffer manager.

> 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?

Can you clarify what you mean?  The number of dependent queries is up to the key set size and the translator properties.

As for then processing the join faster that is up to the join strategy.  If it's just a merge join, then we'll look to have each side fully sorted before processing.  Can you provide the query plan?

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

[TEIID-3131] Broaden aggregate decomposition scenarios - JBoss Issue Tracker expanded support for pushing count(\*).  What version of Teiid are you on?