Hi everybody,
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
select a.*,b*
from a left join b
and in view number 2
select c.*,d*
from c left join d
and then I have to join the two views the final code is the following
select
e.*,
f.*
from
(select a.*,b*
from a left join b) e
left join
(select c.*,d*
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.
Regards
Alessandro