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 when it was returned, is it that much more valuable?

SQL Server计数缓慢

相关推荐