1 Reply Latest reply on Jan 12, 2018 11:56 AM by Steven Hawkins

    How to optimize automatic SQL code generating multiple and nested join?

    Alessandro Ravasio Newbie

      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