SQL Server 计数很慢

2021-12-13 00:00:00 sql count sql-server-2005 sql-server-2008 sql-server

计算数据量大的表可能会很慢，有时需要几分钟；它也可能在繁忙的服务器上产生死锁.我想显示真实值，NOLOCK 不是一个选项.

Counting tables with large amount of data may be very slow, sometimes it takes minutes; it also may generate deadlock on a busy server. I want to display real values, NOLOCK is not an option.

我使用的服务器是 SQL Server 2005 或 2008 Standard 或 Enterprise - 如果重要的话.我可以想象 SQL Server 维护每个表的计数，如果没有 WHERE 子句，我可以很快得到那个数字，对吗?

The servers I use is SQL Server 2005 or 2008 Standard or Enterprise - if it matters. I can imagine that SQL Server maintains the counts for every table and if there is no WHERE clause I could get that number pretty quickly, right?

例如:

SELECT COUNT(*) FROM myTable

应该立即返回正确的值.我需要依靠统计数据来更新吗?

should immediately return with the correct value. Do I need to rely on statistics to be updated?

推荐答案

非常接近(忽略任何进行中的事务)将是:

Very close approximate (ignoring any in-flight transactions) would be:

SELECT SUM(p.rows) FROM sys.partitions AS p
  INNER JOIN sys.tables AS t
  ON p.[object_id] = t.[object_id]
  INNER JOIN sys.schemas AS s
  ON s.[schema_id] = t.[schema_id]
  WHERE t.name = N'myTable'
  AND s.name = N'dbo'
  AND p.index_id IN (0,1);

这将比 COUNT(*) 返回快得多，并且如果您的表变化得足够快，那么它的准确度并没有降低 - 如果您的表在您开始 COUNT 之间发生了变化(并且锁被占用)以及何时返回(当锁被释放并且所有等待的写事务现在都被允许写入表时)，它是否更有价值?我不这么认为.

This will return much, much quicker than COUNT(*), and if your table is changing quickly enough, it's not really any less accurate - if your table has changed between when you started your COUNT (and locks were taken) and when it was returned (when locks were released and all the waiting write transactions were now allowed to write to the table), is it that much more valuable? I don't think so.

如果您有要计算的表的某个子集(例如，WHERE some_column IS NULL)，您可以在该列上创建一个过滤索引，并以一种方式构建 where 子句或其他，取决于它是例外还是规则(因此在较小的集合上创建过滤索引).所以这两个索引之一:

If you have some subset of the table you want to count (say, WHERE some_column IS NULL), you could create a filtered index on that column, and structure the where clause one way or the other, depending on whether it was the exception or the rule (so create the filtered index on the smaller set). So one of these two indexes:

CREATE INDEX IAmTheException ON dbo.table(some_column)
  WHERE some_column IS NULL;

CREATE INDEX IAmTheRule ON dbo.table(some_column)
  WHERE some_column IS NOT NULL;

然后您可以使用类似的方式获得计数:

Then you could get the count in a similar way using:

SELECT SUM(p.rows) FROM sys.partitions AS p
  INNER JOIN sys.tables AS t
  ON p.[object_id] = t.[object_id]
  INNER JOIN sys.schemas AS s
  ON s.[schema_id] = t.[schema_id]
  INNER JOIN sys.indexes AS i
  ON p.index_id = i.index_id
  WHERE t.name = N'myTable'
  AND s.name = N'dbo'
  AND i.name = N'IAmTheException' -- or N'IAmTheRule'
  AND p.index_id IN (0,1);

如果你想知道相反的情况，你只需从上面的第一个查询中减去.

And if you want to know the opposite, you just subtract from the first query above.

相关文章