sql - 可以优化此 SQL 查询以运行得更快吗？

Question

我有一个需要很长时间才能完成的 SQL 查询(对于 SQL Server 2008 R2)。我想知道是否有更好的方法来做到这一点？

SELECT @count = COUNT(Name)
FROM Table1 t
WHERE t.Name = @name AND t.Code NOT IN (SELECT Code FROM ExcludedCodes)

表 1 中有大约 9000 万行，并按名称和代码进行索引。
ExcludedCodes 中只有大约 30 行。

这个查询在一个存储过程中，被调用了大约 40k 次，这个过程完成的总时间是 27 分钟。我相信这是我最大的瓶颈，因为它查询的行数和次数它做到了。

因此，如果您知道优化此功能的好方法，将不胜感激!如果无法优化，那么我想我会坚持 27 分钟...

编辑

我将 NOT IN 更改为 NOT EXISTS 并将时间缩短到 10:59，因此仅此一项对我来说就是一个巨大的收获。我仍然会尝试按照下面的建议执行 group by 语句，但这将需要完全重写存储过程并且可能需要一些时间......(正如我之前所说，我不是最擅长 SQL，但它正在开始在我身上成长。^^)

Answer 1

除了让查询本身更快响应的变通方法之外，您是否考虑过在表中维护一列来说明它是否在此集合中？它需要大量维护，但如果 ExcludedCodes 表不经常更改，最好进行维护。例如，您可以添加一个 BIT 列:

ALTER TABLE dbo.Table1 ADD IsExcluded BIT;

使其 NOT NULL 并默认为 0。然后您可以创建一个过滤索引:

CREATE INDEX n ON dbo.Table1(name)
  WHERE IsExcluded = 0;

现在你只需要更新一次表:

UPDATE t
  SET IsExcluded = 1
  FROM dbo.Table1 AS t
  INNER JOIN dbo.ExcludedCodes AS x
  ON t.Code = x.Code;

并且正在进行中，您必须使用两个表上的触发器来维护它。有了这个，您的查询将变为:

SELECT @Count = COUNT(Name)
  FROM dbo.Table1 WHERE IsExcluded = 0;

编辑

至于“NOT IN比LEFT JOIN慢”这是我仅对几千行执行的简单测试:

编辑 2

我不确定为什么这个查询不会做你想要的，而且比你的 40K 循环效率高得多:

SELECT src.Name, COUNT(src.*)
  FROM dbo.Table1 AS src
  INNER JOIN #temptable AS t
  ON src.Name = t.Name
  WHERE src.Code NOT IN (SELECT Code FROM dbo.ExcludedCodes)
  GROUP BY src.Name;

或 LEFT JOIN 等价物:

SELECT src.Name, COUNT(src.*)
  FROM dbo.Table1 AS src
  INNER JOIN #temptable AS t
  ON src.Name = t.Name
  LEFT OUTER JOIN dbo.ExcludedCodes AS x
  ON src.Code = x.Code
  WHERE x.Code IS NULL
  GROUP BY src.Name;

我会在不到 27 分钟的时间内为这些查询中的任何一个投入资金。我什至建议按顺序运行这两个查询将比您需要 27 分钟的一个查询快得多。

最后，您可能会考虑索引 View 。我不知道您的表结构以及您是否违反了任何限制，但恕我直言，值得调查。