基于日期从多临时表查询数据：现有SQL查询的合理性验证

首先先帮你修正原查询里的小语法问题——语句最后多了一个额外的右括号，正确的语句应该是：

SELECT TOP 1 * 
FROM (
    SELECT B.FILEKEY, B.STATUS AS STATUS, B.EFFDATE FROM TABLEB B 
    UNION 
    SELECT A.FILEKEY, A.STATUS2 AS STATUS, A.EFFDATE2 AS EFFDATE FROM TABLEA A
) C 
WHERE C.EFFDATE <= '5/1/2011'
ORDER BY C.EFFDATE DESC

接下来咱们聊聊优化空间，主要从以下几个方面入手：

• 用UNION ALL替代UNION（如果允许的话）
  UNION会对联合后的结果集做去重操作，这背后需要额外的排序和数据比较开销。如果你的TABLEA和TABLEB中不会出现完全重复的(FILEKEY, STATUS, EFFDATE)数据，直接换成UNION ALL能显著提升性能，避免不必要的去重步骤。

• 提前过滤日期，缩小联合数据集
  原查询是先把两个表的所有数据联合起来，再过滤日期。更好的做法是在每个子查询里就先过滤符合EFFDATE <= '5/1/2011'条件的数据，这样联合的数据集会小很多，后续的排序和查询开销也会降低：

  SELECT TOP 1 * 
  FROM (
      SELECT B.FILEKEY, B.STATUS AS STATUS, B.EFFDATE 
      FROM TABLEB B 
      WHERE B.EFFDATE <= '5/1/2011'
      UNION ALL  -- 确认无重复再用，否则保留UNION
      SELECT A.FILEKEY, A.STATUS2 AS STATUS, A.EFFDATE2 AS EFFDATE 
      FROM TABLEA A
      WHERE A.EFFDATE2 <= '5/1/2011'
  ) C 
  ORDER BY C.EFFDATE DESC
  

• 添加合适的索引
  如果这两个表的数据量较大，给日期字段添加索引能极大加速过滤和排序操作：

  • 给TABLEB的EFFDATE字段建索引，比如：CREATE INDEX IX_TABLEB_EFFDATE ON TABLEB(EFFDATE DESC);
  • 给TABLEA的EFFDATE2字段建索引，比如：CREATE INDEX IX_TABLEA_EFFDATE2 ON TABLEA(EFFDATE2 DESC);
    如果你经常按FILEKEY筛选，还可以建复合索引，比如(FILEKEY, EFFDATE DESC)，进一步优化查询效率。

• 优化TOP 1的获取逻辑
  原查询是先联合所有符合条件的数据，再排序取最新的一条。其实我们可以分别从两个表中先取出符合条件的最新一条，再在这两条里取最新的，这样能大幅减少需要处理的数据量，尤其当表数据量很大时效果明显：

  SELECT TOP 1 *
  FROM (
      -- 从TABLEB取符合条件的最新记录
      SELECT TOP 1 B.FILEKEY, B.STATUS AS STATUS, B.EFFDATE 
      FROM TABLEB B 
      WHERE B.EFFDATE <= '5/1/2011'
      ORDER BY B.EFFDATE DESC
      UNION ALL
      -- 从TABLEA取符合条件的最新记录
      SELECT TOP 1 A.FILEKEY, A.STATUS2 AS STATUS, A.EFFDATE2 AS EFFDATE 
      FROM TABLEA A 
      WHERE A.EFFDATE2 <= '5/1/2011'
      ORDER BY A.EFFDATE2 DESC
  ) C
  ORDER BY C.EFFDATE DESC
  

• 统一日期格式，避免解析歧义
  原查询里用的'5/1/2011'在不同数据库或语言环境下可能被解析成不同的日期（比如5月1日或1月5日），建议用无歧义的日期格式，比如SQL Server里的'20110501'，避免潜在的日期解析错误。

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