I'm a newbie with teiid software and I would like to know something more especially optimization technique.
Currently we are using only sql language to manage data and I noticed that teeid generate a unique code for every virtual view implemented.
So if in view number 1 for example I have this query
from a left join b
and in view number 2
from c left join d
and then I have to join the two views the final code is the following
from a left join b) e
from c left join d) f
the cost of the single join is very small but the final code with the nested join has a very high code.
if we exclude system problems (eg too many processes running at the same time, too many records processed, DB vendors, etc) do you have any suggestion about improving this kind of query?
I used to work with classical ETL procedures, in these case I would have created two physical staging tables instead of the virtual views but I don't think it's the correct use of teiid.
we shoud use java procedures for example?
Tell me if you need more information.
Thanks in advance for your help.
> do you have any suggestion about improving this kind of query?
What does the combined plan look like? If each of the constituent joins are performannt is the issue that a large number of rows are returned by each? What sources do a, b, c, and d reside on? Is there costing metadata associated with the source schemas/models?