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

我在 XXXXXX 数据库中的数十万条记录中遇到了同样的问题。在我的代码中，我想从所有节点检索层次结构（至少包含一个子节点）节点。

写入的初始查询非常慢。

  SELECT SUPPLIER_ID, PARENT_SUPPLIER_ID,
  FROM SUPPLIER
  WHERE 
    SUPPLIER_ID != PARENT_SUPPLIER_ID
    OR 
    SUPPLIER_ID   IN
      (SELECT DISTINCT PARENT_SUPPLIER_ID
       FROM SUPPLIER
       WHERE SUPPLIER_ID != PARENT_SUPPLIER_ID
      );

然后重新写入

  SELECT a.SUPPLIER_ID, a.PARENT_SUPPLIER_ID,
  FROM SUPPLIER a
  LEFT JOIN
  (SELECT DISTINCT PARENT_SUPPLIER_ID
  FROM SUPPLIER
  WHERE SUPPLIER_ID != PARENT_SUPPLIER_ID
  ) b
  ON a. SUPPLIER_ID     = b.PARENT_SUPPLIER_ID
  WHERE a. SUPPLIER_ID != a.PARENT_SUPPLIER_ID
     OR a. SUPPLIER_ID     = b.PARENT_SUPPLIER_ID;

Answer 2

使用 IN 实际上会强制您进行表扫描。当表增长时，执行时间也会增长。此外，您还对返回的每条记录运行该查询。使用标量选择作为表会更容易：

SELECT t.TradeId, t.Type, t.Symbol, t.TradeDate, 
       SUM(TradeLine.Notional) / 1000 AS Expr1
FROM   Trade t,
(SELECT     TradeId, PairOffID
                        FROM          TradeLine AS TradeLine_1
                        WHERE      (TradeDate <= '2011-05-11')
                        GROUP BY PairOffId
                        HAVING      (SUM(Notional) <> 0)) tl       
WHERE  t.TradeId = tl.TradeId
  and  t.id <> tl.PairOffID
GROUP BY Trade.TradeId, Trade.Type, Trade.Symbol, Trade.TradeDate
ORDER BY Trade.Type, Trade.TradeDate

Answer 3

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

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

Answer 4

当子查询开始返回太大的结果集时，我很喜欢临时表。

因此，您的 where 子句将

Where TradeLine.Id In (Select PairOffId From #tempResults)

被定义为 #tempResults （警告：语法来自内存，这意味着可能存在错误）

Select PairOffId Into #tempResults
From TradeLine
Where (TradeDate <= @TradeDate) 
  //I prefer params in case the query becomes a StoredProc
Group By PairOffId
Having (Sum(Notional) <> 0)

Answer 5

我有两个建议你可以尝试：

1）。使用 Exists 因为您不需要从子查询获取数据，如下所示：

存在的地方（从 TradeLine 选择 1
AS TradeLine_1
其中 TradeLine.Id = TradeLine_1.PairOffId
-- 继续您的子查询...)

2).例如，主查询连接到您的子查询

...加入 (your_subquery)
your_subquery.PairOffId = TradeLine.Id

我相信这两种方式可以实现比“In”操作更好的性能。