2 Replies Latest reply on Oct 18, 2012 3:15 PM by tanmoypalit

    Performance Issue after joining with TEMP table

    tanmoypalit Newbie

      I am facing serious performence issue with the following procedure:

       

      CREATE VIRTUAL PROCEDURE

      BEGIN

                DECLARE string VARIABLES.INPUT_ENDPOINT;

                VARIABLES.INPUT_ENDPOINT = XXX_View.get_XXX_STATISTICS.indexList;

                CREATE LOCAL TEMPORARY TABLE VT_LIST (INDEX string);

                INSERT INTO VT_LIST (VT_LIST.INDEX ) VALUES (VARIABLES.INPUT_ENDPOINT);

                SELECT * FROM VT_LIST AS X, XXX_STATISTICS WHERE XXX_View.XXX_STATISTICS.INDEX_IDENTIFIER = X.INDEX ;

      END

       

      Here XXX_STATISTICS is view created from original database. I tried with only one INDEX for now.

       

      The Actual Query which working pretty efficiently is the following one:

      SELECT * FROM XXX_STATISTICS WHERE INDEX_IDENTIFIER  IN ('1212121255648')  

      The reason of using TEMP table is that I want to get rid of IN clause as I have a huge list of INDEXs (more than few thousands).

       

      The difference between the above query and procedure is almost 3-4 min which is not acceptable but also need to make sure more than few thousands indexes are acceptable.

       

      I will appreciate any help on this.

       

      Regards

      Tanmoy Palit