5 Replies Latest reply on Apr 16, 2015 1:20 PM by Mark Addleman

    Parallel executions to same source?

    Mark Addleman Master

      I have a query of form

      select a.c1, a.c2, b.blah

      from a,

           table(select * from b where b.c1=a.c1) b;

      (this form is required because the underlying API for source b requires c1 be specified.)

       

      I'd like Teiid to pushdown executions to b in parallel to improve overall performance.  Any thoughts?

        • 1. Re: Parallel executions to same source?
          Mark Addleman Master

          I should also add that I'm using embedded server and that I thought Teiid would naturally push down in parallel so I may have just messed up the configuration

          • 2. Re: Parallel executions to same source?
            Ramesh Reddy Master

            May be using the hint MAKENOTDEP on the "b" will prohibit in making a dependent join?

            • 3. Re: Parallel executions to same source?
              Steven Hawkins Master

              > I'd like Teiid to pushdown executions to b in parallel to improve overall performance.  Any thoughts?

               

              Using the nested table contruct while logically correct does not force dependent join style behavior.  This is because generally the left hand side will produce few rows and the right hand side is a nested table function, procedure, or other expensive operation.

               

              So you should have an access pattern on b.c1, use the makeind hint on b, or the makedep hint on a rather than using the nested table.  That will give you a dependent join plan that can be parallelized.

              • 4. Re: Parallel executions to same source?
                Mark Addleman Master

                Hi guys.  Adding the access pattern and changing the query to

                select a.c1, a.c2, b.blah

                from a inner join /*+ makedep */ b on b.c1=a.c1;

                nearly did the trick.  Ultimately, I am trying to drive a bunch of parallel API requests which must qualify b.c1.  Under the new access pattern and hint, I get multiple queries pushed down of the form SELECT b.blah FROM b WHERE b.c1 IN (...).  If supports IN is false or the max IN criteria size is set to 1, the push down query is something like SELECT b.blah FROM b WHERE b.c1=x OR b.c1=y...  Weirdly, I get this form even if supportsOrCriteria is false. 

                 

                If supportsInCriteria and supprotsOrCriteria are both false, I would have expected the push down to be a bunch of queries like

                1. SELECT b.blah FROM b WHERE b.c=x
                2. SELECT b.blah FROM b WHERE b.c=y
                3. ...
                • 5. Re: Parallel executions to same source?
                  Mark Addleman Master

                  I just discovered that setting

                    setMaxInCriteriaSize(1);

                    setMaxDependentInPredicates(1);

                  in my translator does the trick.

                   

                  Thanks guys!