如何优化跨库嵌套查询以提升执行速度？

问题详情 #

我从SQL Server数据库查询外部Oracle数据库时遇到性能瓶颈：

• 无WHERE子句时，返回约190000条记录耗时约55秒；
• 添加WHERE子句（过滤指定作业号）后，耗时在30秒到2.5分钟之间波动。

经排查，性能问题出在最后关联的嵌套查询部分——移除该部分后，查询仅需约2秒，但我不清楚如何优化这部分逻辑。该查询通过存储过程调用，@jobNumber为存储过程参数，原查询语句如下：

SELECT * FROM OPENQUERY(externaldb,'SELECT
t1.SERIAL,
t3.PART_NUM,
t2.SUFFIX,
t3.JOB,
t2.DESC,
t2.MODEL,
t2.QTY,
t2.UNIT,
t2.LOCATION,
t6.STATUS,
t1.DESTINATION,
t1.ORDER,
t1.PURCHASER,
t1.PHONE,
t1.CUSTOMER_ID
FROM EXTERNALDB.SERIALS t1
INNER JOIN EXTERNALDB.DELIVERIES t2 ON t1.SERIAL = t2.SERIAL
INNER JOIN EXTERNALDB.DESC t3 ON t2.PART_NUM = t3.PART_NUM
INNER JOIN (SELECT PART, STATUS
            FROM (SELECT
                       t4.SUFFIX,
                       t4.STATUS_TYPE,
                       t4.STATUS_DATE,
                       t4.ID,
                       t4.PART_NUM PART,
                       t4.STATUS,
                       ROW_NUMBER()
                            OVER (PARTITION BY t4.PART_NUM
                            ORDER BY
                                 t4.SUFFIX,
                                 t4.STATUS_TYPE,
                                 t4.STATUS_DATE,
                                 t4.ID) RN
                   FROM EXTERNALDB.STATUSES t4) t5
                   WHERE RN = 1) t6 ON t3.PART_NUM = PART
WHERE t3.JOB = '''''+@jobNumber+'''''')

优化建议 #

• 缩小关联范围：将WHERE t3.JOB = '''''+@jobNumber+'''''的过滤逻辑提前，先筛选出指定作业的所有PART_NUM，再基于这些PART_NUM去关联STATUSES表，避免子查询全表扫描所有状态记录。
• 添加针对性索引：在Oracle端的EXTERNALDB.STATUSES表上创建复合索引：

  CREATE INDEX idx_statuses_part ON STATUSES (PART_NUM, SUFFIX, STATUS_TYPE, STATUS_DATE, ID) INCLUDE (STATUS);
  

  让ROW_NUMBER()的分区和排序可以直接利用索引，减少计算开销。
• 改用APPLY关联：把嵌套子查询替换为CROSS APPLY，只针对主查询中已过滤的PART_NUM取对应第一条状态记录，示例如下：

  SELECT * FROM OPENQUERY(externaldb,'SELECT
  t1.SERIAL,
  t3.PART_NUM,
  t2.SUFFIX,
  t3.JOB,
  t2.DESC,
  t2.MODEL,
  t2.QTY,
  t2.UNIT,
  t2.LOCATION,
  t6.STATUS,
  t1.DESTINATION,
  t1.ORDER,
  t1.PURCHASER,
  t1.PHONE,
  t1.CUSTOMER_ID
  FROM EXTERNALDB.SERIALS t1
  INNER JOIN EXTERNALDB.DELIVERIES t2 ON t1.SERIAL = t2.SERIAL
  INNER JOIN EXTERNALDB.DESC t3 ON t2.PART_NUM = t3.PART_NUM
  CROSS APPLY (
      SELECT STATUS
      FROM EXTERNALDB.STATUSES t4
      WHERE t4.PART_NUM = t3.PART_NUM
      ORDER BY t4.SUFFIX, t4.STATUS_TYPE, t4.STATUS_DATE, t4.ID
      FETCH FIRST 1 ROW ONLY
  ) t6
  WHERE t3.JOB = '''''+@jobNumber+'''''')
  

• 减少数据传输量：原查询使用SELECT * FROM OPENQUERY，尽量明确指定需要的字段，避免跨库传输不必要的数据。
• 分析Oracle执行计划：在Oracle数据库中单独执行OPENQUERY内部的SQL语句，查看执行计划，确认STATUSES表是否存在全表扫描，以及索引是否被正确使用。

内容的提问来源于stack exchange，提问作者Baker6Romeo