java - 需要帮助优化涉及数百万条记录的非常慢的 DB2 SQL 查询

Question

我正在尝试优化以下 SQL，但我对 SQL 优化的知识还很陌生，而且没有取得太大进展。(由于公司政策，我概括了列和其他标识符)在当前状态下，此 SQL根据负载情况，运行时间为 1 到 2 分钟。 VKTINFO 表包含大约 100 万条记录，GNTINFO 表包含大约 300 万条记录。通常情况下，如果这是一个批处理过程，1-2 分钟不会有什么大问题，但我们有代理需要尽快实时提供这些信息 - 更糟糕的是，我们的系统最终超时并向用户。但是，不能选择延长超时窗口。我们还有其他搜索标准，例如名字、邮政编码、帐户类型、帐户状态等，但是当执行以下广泛搜索时，查询会变得相当慢。

如果有任何关于如何操作此 SQL 来加速选择的建议/技术，我将非常感谢对此事的任何想法。如果需要更多信息，我很乐意提供尽可能多且符合我们公司政策的信息。

编辑:此处要求的是 VKTINFO 和 GNTINFO 表的索引。

• 帐号
• expiration_date
• 生效日期

gnt_account_info 和 vkt_account_info 的索引:

• pi_account_num
• pi_policy_num_gid

gntnad 和 vktnad 表的索引:

• nad_account_number
• nad_name_type

gntpolrf 和 vktpolrf 表的索引:

• xrf_account_number

select
processing_system,
total_premium,
quote_by,
email_address,
account_number,
expiration_date,
account_state,
xrf_file,
customer_name
from
(
   select
   'ABCD' as processing_system,
   total_premium,
   quote_by,
   email_address,
   account_number,
   expiration_date,
   account_state,
   xrf_file,
   customer_name
   from vktinfo 
    left outer join vkt_account_info on account_number = pi_account_number 
    left outer join vktpolrf on account_number = xrf_account_number 
    left outer join VKTNAD on account_number = nad_account_number
    and history_expiration_date=nad_history_expiration_date
    and nad_name_type='HA'
   WHERE effective_date >= '2013-02-01'
   AND effective_date <= '2013-02-28'
   AND customer_name like '_SMITH%'
   AND account_state = 'South Carolina'
   union all
   select
   'EFGH' as processing_system,
   total_premium,
   quote_by,
   email_address,
   account_number,
   expiration_date,
   account_state,
   xrf_file,
   customer_name
   from gntinfo 
    left outer join gnt_account_info on account_number = pi_account_number 
    left outer join vktpolrf on account_number = xrf_account_number 
    left outer join GNTNAD on account_number = nad_account_number
    and history_expiration_date=nad_history_expiration_date
    and nad_name_type='HA'
   WHERE effective_date >= '2013-02-01'
   AND effective_date <= '2013-02-28'
   AND customer_name like '_SMITH%'
   AND account_state = 'South Carolina'
)
a
order by customer_name ASC fetch first 1000 rows only WITH UR

Answer 1

我没有给你一个绝对可靠的答案。但我确实有一些事情你可以尝试。我了解您无权获取执行计划。

• 与去过那里一段时间的人核实，并询问您是否应该能够运行 EXPLAIN。
• 您可能需要 account_state 的索引。经验法则:为连接条件或 WHERE 子句中使用的每一列建立索引。有时多列索引的性能比多个单列索引要好。
• 尝试将子查询的 WHERE 子句中您可以移动到外部查询的每个部分，并测试两件事。
  • 在外部查询的普通 WHERE 子句中使用这些部分。
  • 重新排列外部查询，以便您可以对其执行内部联接，而不是从 UNION 子查询中选择。
• 确定是否有任何左外连接可以被内连接替换。存储“nad_name_type”的表可能是内连接的候选表。 (你明白为什么吗？)
• 测试子查询作为 View 实现时的性能。您可能需要 DBA 的帮助。 (如果他们不允许您运行 EXPLAIN，那么他们可能也不允许您创建 View 。)
• 测试子查询作为具体化查询表实现时的性能。您可能还需要 DBA 的帮助。