3 Replies Latest reply on Jan 2, 2013 2:59 PM by tanmoypalit

    Performance Issue with IN clause when used with TEMP Table

    tanmoypalit

      Hi,

       

      I have the following procedure which needs to be parameterized:

      CREATE VIRTUAL PROCEDURE

      BEGIN

                SELECT * FROM STATS_XML.CHEMISTRY_TRANSACTIONDocument WHERE STATS_XML.CHEMISTRY_TRANSACTIONDocument.CHEMISTRY_TRANSACTION.CHEM_STATS.@Unique_Identifier IN ('1200008', '1200011');

      END

       

      Procedure produces an XML dcoument and IN clause values need to be parameterized. I created the following new procedure:

      CREATE VIRTUAL PROCEDURE

      BEGIN

                DECLARE string VARIABLES.UI_LIST;

                VARIABLES.UI_LIST = REPLACE(Dev_View.getSTATISTICS.UILIST, ',', CHAR(10));

                CREATE LOCAL TEMPORARY TABLE VT_LIST (UI string);

                INSERT INTO VT_LIST (VT_LIST.UI) SELECT * FROM TEXTTABLE(VARIABLES.UI_LIST COLUMNS col1 string) AS UI_COL;

                SELECT * FROM STATS_XML.CHEMISTRY_TRANSACTIONDocument WHERE STATS_XML.CHEMISTRY_TRANSACTIONDocument.CHEMISTRY_TRANSACTION.CHEM_STATS.@Unique_Identifier IN (SELECT VT_LIST.UI FROM VT_LIST);

      END

       

      Allthough the new proc works but performance is really bad. The first one took only couple of seconds to execute and the new proc took more than 2 min to execute with the same parameters (both the query returns only 17 records).

      Any thoughts?

      Is there any better way of replacing the IN clause?

       

      Thanks for the help!!

       

      Regards

      Tanmoy Palit

        • 1. Re: Performance Issue with IN clause when used with TEMP Table
          tanmoypalit

          Got a very ugly way for doing this but it works with better performance:

          CREATE VIRTUAL PROCEDURE

          BEGIN

                    DECLARE string VARIABLES.UI_LIST;

                    DECLARE string VARIABLES.query;

                    VARIABLES.UI_LIST = REPLACE(Dev_View.getSTATISTICS.UILIST, ',', ''',''');

                    VARIABLES.query = (('SELECT * FROM STATS_XML.CHEMISTRY_TRANSACTIONDocument WHERE STATS_XML.CHEMISTRY_TRANSACTIONDocument.CHEMISTRY_TRANSACTION.CHEM_STATS.@Unique_Identifier IN (''' || VARIABLES.UI_LIST) || ''')');

                    EXECUTE IMMEDIATE VARIABLES.query;

          END

           

           

          -Tanmoy

          • 2. Re: Performance Issue with IN clause when used with TEMP Table
            shawkins

            The issue is with the optimizer not understanding how many records are in the temp table when the xml statement plan is compiled (which is happending prior to execution).  Thus the non-performing plan is likely delaying the effect of the identifier filtering to a later join and pulling back too many records. 

             

            The use of dynamic sql should also be effective even with the IN subquery as the statement plan will be made when the temp table record count is known.  Another approach on Teiid 7.4+ would be to use the dependent join hint for the subquery:  "/*+ DJ */ (SELECT VT_LIST.UI FROM VT_LIST)", which instructs the planner to use the IN list as a filter first.  See the hints section of the Reference - https://docs.jboss.org/author/display/TEIID/Subquery+Optimization

             

            Ideally we should be automatically handling this case as we should guess at your intent, rather than forcing you to be explicit and/or allowing for the use of something like the SQL Server "WITH RECOMPILE" option to indicate that a query should be replanned.  Can you log an issue on more optimistic dependent join planning?

             

            Steve

            • 3. Re: Performance Issue with IN clause when used with TEMP Table
              tanmoypalit

              Thanks Steven.

              /*+ DJ */ (SELECT VT_LIST.UI FROM VT_LIST) worked well.

               

              Issue created:

              https://issues.jboss.org/browse/TEIIDDES-1552

               

              Regards

              Tanmoy Palit