About cookies on this site Our websites require some cookies to function properly (required). In addition, other cookies may be used with your consent to analyze site usage, improve the user experience and for advertising. For more information, please review your options. By visiting our website, you agree to our processing of information as described in IBM’sprivacy statement. To provide a smooth navigation, your cookie preferences will be shared across the IBM web domains listed here.
Last updated: Nov 26, 2024
If you run a query that has subqueries, you might encounter performance issues.
Symptoms
You run a query that has subqueries, but the query takes too long to return results.Resolving the problem
To solve this issue,
- If nickname parallel fetching is enabled, go to the generated access plan.
- Check that your subquery is rewritten into a join in the SQL statement as shown in the following example.
- Original SQL statement
-
SELECT A5."CD_GENDER" C0, A5."CD_MARITAL_STATUS" C1, A5."CD_EDUCATION_STATUS" C2, A5."CD_PURCHASE_ESTIMATE" C3, A5."CD_CREDIT_RATING" C4, A5."CD_DEP_COUNT" C5, A5."CD_DEP_EMPLOYED_COUNT" C6, A5."CD_DEP_COLLEGE_COUNT" C7 FROM "TPCDS1TB"."CUSTOMER_ADDRESS" A1, "TPCDS1TB"."CUSTOMER" A2, "TPCDS1TB"."CUSTOMER_DEMOGRAPHICS" A5 WHERE (A1."CA_COUNTY" IN ('Allen County', 'Rock County', 'Modoc County', 'Yamhill County', 'Lee County')) AND (A2."C_CURRENT_ADDR_SK" = A1."CA_ADDRESS_SK") AND (1 = (SELECT DISTINCT 1 FROM "TPCDS1TB"."STORE_SALES" A3, "TPCDS1TB"."DATE_DIM" A4 WHERE (A2."C_CUSTOMER_SK" = A3."SS_CUSTOMER_SK") AND (A3."SS_SOLD_DATE_SK" = A4."D_DATE_SK") AND (A4."D_YEAR" = 1999) AND (2 <= A4."D_MOY") AND (A4."D_MOY" <= 5) ))
- Rewritten SQL statement
-
(SELECT Q3.CD_GENDER, Q3.CD_MARITAL_STATUS, Q3.CD_EDUCATION_STATUS, Q3.CD_PURCHASE_ESTIMATE, Q3.CD_CREDIT_RATING, Q3.CD_DEP_COUNT, Q3.CD_DEP_EMPLOYED_COUNT, Q3.CD_DEP_COLLEGE_COUNT FROM TPCDS1TB.CUSTOMER_ADDRESS AS Q1, TPCDS1TB.CUSTOMER AS Q2, TPCDS1TB.CUSTOMER_DEMOGRAPHICS AS Q3, (SELECT DISTINCT 1 FROM TPCDS1TB.DATE_DIM AS Q4, TPCDS1TB.STORE_SALES AS Q5 WHERE (Q4.D_MOY <= 5) AND (2 <= Q4.D_MOY) AND (Q4.D_YEAR = 1999) AND (Q5.SS_SOLD_DATE_SK = Q4.D_DATE_SK) AND (Q2.C_CUSTOMER_SK = Q5.SS_CUSTOMER_SK) ) AS Q6,
- At the end of all values of the DB2_DV_OVERRIDES parameter, add the
NO_SUBQPPD_MPP value. For example, if the current values of the
DB2_DV_OVERRIDES parameter are as
follows.
DB2_DV_OVERRIDES=EN_OJMQT,EN_JOINFILTER,EN_GAIANQUERY_JOINFILTER,
After you add the NO_SUBQPPD_MPP value, the parameter values are shown in the following example.DB2_DV_OVERRIDES=EN_OJMQT,EN_JOINFILTER,EN_GAIANQUERY_JOINFILTER,NO_SBQPPD_MPP
You can set the DB2_DV_OVERRIDES parameter at the instance level, so that the parameter applies to all queries, or at the query level, so that the parameter applies to a particular query. To do so, you can apply optimization profiles (OPTGUIDELINE) to a specific query. For more information, see SQL compiler registry variables in an optimization profile.
Was the topic helpful?
0/1000