INSERT INTO with SELECT subquery db2 9.5

1 vote

2 answers

14030 views

performance db2 db2-9.5 query-performance

                          I have the following query that takes 4 seconds to execute:

    SELECT TX.* FROM TABLE_X TX
      INNER JOIN TABLE_Y TY
        ON TY.VALUE_ID = VALUE_ID_IN
          AND TY.PROD_ID = TX.PROD_ID
    WHERE (TX.PLACE_ID1 = VAR_PLACE_ID OR TX.PLACE_ID2 = PLACE_ID)
      AND (TX.DATE > TY.FIRST_DATE)
      AND (TX.TYPE_ID != VAR_TYPE_ID1_IN AND TX.TYPE_ID != VAR_TYPE_ID2_IN)
      AND (TX.COL1 != COL1_IN OR TX.COL2 != COL2_IN)

However, I want to store it in a TEMPORARY TABLE.  When I execute the following query it takes 50 seconds:


    INSERT INTO SESSION.TEMP_TABLE_X   /*new line to store result*/
        SELECT TX.* FROM TABLE_X TX
          INNER JOIN TABLE_Y TY
            ON TY.VALUE_ID = VALUE_ID_IN
              AND TY.PROD_ID = TX.PROD_ID
        WHERE (TX.PLACE_ID1 = VAR_PLACE_ID OR TX.PLACE_ID2 = PLACE_ID)
          AND (TX.DATE > TY.FIRST_DATE)
          AND (TX.TYPE_ID != VAR_TYPE_ID1_IN AND TX.TYPE_ID != VAR_TYPE_ID2_IN)
          AND (TX.COL1 != COL1_IN OR TX.COL2 != COL2_IN)

TABLE_X = 300 000 rows

TABLE_Y =  90 000 rows

How can I reduce the 50 seconds running time?

The server is running DB2 v9.5.
                        

Asked by Logar314159 (123 rep)

Nov 5, 2013, 12:12 AM
Last activity: Dec 12, 2013, 08:04 AM

INSERT INTO with SELECT subquery db2 9.5

Related Questions