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

    subquery in teiid

    meltedmetal
    meltedmetal Mar 31, 2011 7:56 AM

      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
          rareddy
          rareddy Mar 31, 2011 10:50 AM (in response to meltedmetal)

          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..

            • Actions
          • 2. subquery in teiid
            shawkins
            shawkins Mar 31, 2011 4:09 PM (in response to rareddy)

            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

              • Actions
            • 3. subquery in teiid
              meltedmetal
              meltedmetal Apr 1, 2011 1:41 AM (in response to shawkins)

              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.

                • Actions
              • 4. subquery in teiid
                shawkins
                shawkins Apr 1, 2011 3:32 PM (in response to meltedmetal)

                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);

                  • Actions