4 Replies Latest reply on Apr 1, 2011 3:32 PM by Steven Hawkins

    subquery in teiid

    S.Q. R Newbie

      Hi, All

       

      I am using Teiid 7.4 Beta.

       

      My case is that:

      Table 'A' includes 10 records. This table is from data source 'ds_1'.

      Table 'B' includes 100000 records. This table is from data source 'ds_2'.

       

      In VDB 'vdb1', there is a table 'C'. Its select transformation is below :

      select * from B where B.id in (select id from A);
      

       

      In query plan, I found that teiid will load all 100000 records of table 'B'.

       

      It affects performance very much.

       

      How can I improve the performance?

       

      Any tips are welcome.

       

      Thank you.

       

       

      Best Regards,

      S.Q

        • 1. subquery in teiid
          Ramesh Reddy Master

          S.Q,

           

          There are couple different ways you can do this.

           

          1) Define costing information on the tables in the Designer (look at Cardinality property on View Table). What you are defining is roughly how rows of data is present in this table.

          2) Or use hints like MAKEDEP.

           

          Search for costing and MAKEDEP in the Reference guide.

           

          Ramesh..

          • 2. subquery in teiid
            Steven Hawkins Master

            MAKEDEP does not by default apply to subqueries. 

             

            There are a couple of things you can do.

             

            You can set the system property:  org.teiid.subqueryUnnestDefault=true

             

            This will direct the rewriter to change this query (and many other situations involving subqueries) into:

             

            select * from B, (select [distinct] id from A) AS X WHERE where B.id = X.id

             

            If you have marked also marked by as dependent or you have the costing set as you have stated, then the result will be a dependent join.

             

            Alternatively you can use the /*+ mj */ hint instead of setting the org.teiid.subqueryUnnestDefault property. 

             

            select * from B where B.id in /*+mj*/ (select id from A);

             

            Here again you would need to have the costing set appropriately or have be marked as dependent.

             

            Steve

            • 3. subquery in teiid
              S.Q. R Newbie

              Thanks Steve & Ramesh!

               

              I have tried MAKEDEP for join query.

              But I am not clear that how MAKEDEP is used in subquery.

              Where do I need to put the key word 'MAKEDEP' in the SQL?

              Would you like give me a example for the below SQL?

              select * from B where B.id in /*+mj*/ (select id from A);

               

              Best Regards,

              S.Q.

              • 4. subquery in teiid
                Steven Hawkins Master

                If you have the cardinalities set on the tables in your model, then an additional should not be needed.

                 

                Otherwise use:

                 

                select * from B MAKEDEP where B.id in /*+mj*/ (select id from A);