Performance Issue after joining with TEMP table

[tanmoypalit]

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

[shawkins]

Tanmoy,

You'll want to use a hint MAKEDEP or MAKEIND to push the temp table values via a dependent join:

{code}SELECT * FROM VT_LIST AS X, /*+ MAKEDEP */ XXX_STATISTICS WHERE XXX_View.XXX_STATISTICS.INDEX_IDENTIFIER = X.INDEX{code}

There is a soft spot in optimization when using temp tables in a procedure.  We precompile the procedure plan so at the time we're planning the join, even with just a single value being inserted, the optimizer does not know to automatically use a dependent join.

Steve

[tanmoypalit]

Thanks Steven!!

This works great.

Regards

Tanmoy Palit