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