SQL计数（*）性能 [英] SQL count(*) performance

问题描述

我有一个SQL表BookChapters与超过2000万行。它有一个聚集主键（bookChapterID），没有任何其他键或索引。运行以下查询需要几毫秒

  if（select count（*）from BookChapters）= 0 
 .. 。
  

但是，当我更改它时，需要10分钟以上

  if（select count（*）from BookChapters）= 1 
 ... 
  

或

  if（select count（*）from BookChapters） > 1 
 ... 
  

为什么？
如何获得 select count（*）以更快地执行？

解决方案

Mikael Eriksson有一个很好的解释，为什么第一个查询是快速的：

SQL服务器优化它：
如果存在（select * from BookChapters）。所以它寻找一行的存在，而不是计数表中的所有行。

对于其他两个查询，SQL Server将使用以下规则。要执行 SELECT COUNT（*）的查询，SQL Server将使用最窄的
非聚集索引来计算行数。如果表没有任何
非聚集索引，它将不得不扫描表。

此外，如果你的表有一个 索引，您可以使用以下查询（从此网站借用 Get Row Counts Fast！）

   -  SQL Server 2005/2008 
 SELECT OBJECT_NAME .id）[Table_Name]，i.rowcnt [Row_Count] 
 FROM sys.sysindexes i WITH（NOLOCK）
 WHERE i.indid in（0,1）
 ORDER BY i.rowcnt desc 
 
  -  SQL Server 2000 
 SELECT OBJECT_NAME（i.id）[Table_Name]，i.rows [Row_Count] 
 FROM sysindexes i（NOLOCK）
 WHERE i .indid in（0,1）
 ORDER BY i.rows desc 
  

它使用sysindexes系统表。更多信息，您可以在这里找到 SQL Server 2000 ， SQL Server 2005 ， SQL Server 2008 ， SQL Server 2012

这是另一个链接为什么我的SELECT COUNT（*）运行这么慢？它显示了当您右键单击表并选择属性时，Microsoft使用快速显示权限的技术。

  select sum （spart.rows）
 from sys.partitions spart 
其中spart.object_id = object_id（'YourTable'）
和spart.index_id< 2 
  

你应该会发现，无论你有多少表格，

如果你使用SQL 2000，你可以使用sysindexes表来获取数字。

  select max（ROWS）
 from sysindexes 
其中id = object_id（'YourTable'）
  

b $ b

根据SQL更新sysindexes表的频率，这个数字可能会稍微有些偏差，但通常情况下（或至少足够接近），这个数字会有所不同。

I have a SQL table BookChapters with over 20 millions rows. It has a clustered primary key (bookChapterID) and doesn't have any other keys or indexes. It takes miliseconds to run the following query

if (select count(*) from BookChapters) = 0
...

However, it takes over 10 minutes when I change it like so

if (select count(*) from BookChapters) = 1
...

or

if (select count(*) from BookChapters) > 1
...

Why is that? How can I get select count(*) to execute faster?

解决方案

Mikael Eriksson has a good explanation bellow why the first query is fast:

SQL server optimize it into: if exists(select * from BookChapters). So it goes looking for the presence of one row instead of counting all the rows in the table.

For the other two queries, SQL Server would use the following rule. To perform a query like SELECT COUNT(*), SQL Server will use the narrowest non-clustered index to count the rows. If the table does not have any non-clustered index, it will have to scan the table.

Also, if your table has a clustered index you can get your count even faster using the following query (borrowed from this site Get Row Counts Fast!)

--SQL Server 2005/2008
SELECT OBJECT_NAME(i.id) [Table_Name], i.rowcnt [Row_Count]
FROM sys.sysindexes i WITH (NOLOCK)
WHERE i.indid in (0,1)
ORDER BY i.rowcnt desc

--SQL Server 2000
SELECT OBJECT_NAME(i.id) [Table_Name], i.rows [Row_Count]
FROM sysindexes i (NOLOCK)
WHERE i.indid in (0,1)
ORDER BY i.rows desc

It uses sysindexes system table. More info you can find here SQL Server 2000, SQL Server 2005, SQL Server 2008, SQL Server 2012

Here is another link Why is my SELECT COUNT(*) running so slow? with another solution. It shows technique that Microsoft uses to quickly display the number of rights when you right click on the table and select properties.

select sum (spart.rows)
from sys.partitions spart
where spart.object_id = object_id(’YourTable’)
and spart.index_id < 2

You should find that this returns very quickly no matter how many tables you have.

If you are using SQL 2000 still you can use the sysindexes table to get the number.

select max(ROWS)
from sysindexes
where id = object_id(’YourTable’)

This number may be slightly off depending on how often SQL updates the sysindexes table, but it’s usually corrent (or at least close enough).

这篇关于SQL计数（*）性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！