Performance issues in queries with subqueries in Data Virtualization

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.