sql - WHERE子句包含IN(子查询)的查询性能

Question

SELECT Trade.TradeId, Trade.Type, Trade.Symbol, Trade.TradeDate, 
       SUM(TradeLine.Notional) / 1000 AS Expr1
FROM   Trade INNER JOIN
             TradeLine ON Trade.TradeId = TradeLine.TradeId
WHERE  (TradeLine.Id IN
                      (SELECT     PairOffId
                        FROM          TradeLine AS TradeLine_1
                        WHERE      (TradeDate <= '2011-05-11')
                        GROUP BY PairOffId
                        HAVING      (SUM(Notional) <> 0)))
GROUP BY Trade.TradeId, Trade.Type, Trade.Symbol, Trade.TradeDate
ORDER BY Trade.Type, Trade.TradeDate

当表开始增长时，我担心 WHERE 子句中 IN 的性能。对于这种查询，有人有更好的策略吗？子查询返回的记录数的增长速度比 TradeLine 表中的记录数增长慢得多。 TradeLine 表本身以 10/天的速度增长。

谢谢。

编辑:我使用了将子查询从 WHERE 移至 FROM 的想法。我对促成这个新查询的所有答案都投票了。

   SELECT Trade.TradeId, Trade.Type, Trade.Symbol, Trade.TradeDate,   
          PairOff.Notional / 1000 AS Expr1
   FROM         Trade INNER JOIN
                  TradeLine ON Trade.TradeId = TradeLine.TradeId INNER JOIN
                      (SELECT     PairOffId, SUM(Notional) AS Notional
                        FROM          TradeLine AS TradeLine_1
                        WHERE      (TradeDate <= '2011-05-11')
                        GROUP BY PairOffId
                   HAVING (SUM(Notional) <> 0)) AS PairOff ON TradeLine.Id = PairOff.PairOffId
   ORDER BY Trade.Type, Trade.TradeDate

Answer 1

IN 子句中的子查询不依赖于外部查询中的任何内容。您可以安全地将其移至 FROM 子句中；一个理智的查询计划构建器会自动完成它。

此外，必须对要在生产中使用的任何查询调用 EXPLAIN PLAN。执行此操作并查看 DBMS 对此查询计划的看法。