0 / 0
Performance issues in queries with subqueries in Watson Query

Performance issues in queries with subqueries in Watson Query

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,
  1. If nickname parallel fetching is enabled, go to the generated access plan.
  2. 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,
  3. 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.

Generative AI search and answer
These answers are generated by a large language model in watsonx.ai based on content from the product documentation. Learn more