Data Virtualization中带有子查询的查询的性能问题

Go back to the English version of the documentation

Last updated: 2024年11月26日

Data Virtualization中带有子查询的查询的性能问题

如果运行包含子查询的查询，可能会遇到性能问题。

症状

您运行包含子查询的查询，但该查询返回结果所用时间太长。

解决问题

要解决此问题：

如果启用了昵称并行访存，请转至生成的存取方案。

检查子查询是否已重写为 SQL 语句中的连接，如以下示例中所示。

原始 SQL 语句

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)
))

重写的 SQL 语句

(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,

在 DB2_DV_OVERRIDES 参数的所有值的末尾，添加 NO_SUBQPPD_MPP 值。例如，如果 DB2_DV_OVERRIDES 参数的当前值如下所示。
```
DB2_DV_OVERRIDES=EN_OJMQT,EN_JOINFILTER,EN_GAIANQUERY_JOINFILTER,
```
添加 NO_SUBQPPD_MPP 值后，参数值将显示在以下示例中。
```
DB2_DV_OVERRIDES=EN_OJMQT,EN_JOINFILTER,EN_GAIANQUERY_JOINFILTER,NO_SBQPPD_MPP
```
可以在实例级别设置 DB2_DV_OVERRIDES 参数，以便该参数应用于所有查询，或者在查询级别进行设置，以便该参数应用于特定查询。为此，可以将优化概要文件 (OPTGUIDELINE) 应用于特定查询。有关更多信息，请参阅优化概要文件中的 SQL 编译器注册表变量。