SQL：使COUNT（*）> 1高​​效 [英] SQL: Making COUNT(*) > 1 Efficient

问题描述

如果您想知道 COUNT（*）> 0，那么您可以使用 EXISTS 更高效。是否有一种方法，当我想知道 COUNT（*）> 1？

时， p>（需要与SQL Server和Oracle兼容。）

感谢Jamie

我试图提高某些代码的性能。有一些类似于：

  if（SQL（'SELECT COUNT（*）FROM table WHERE a = b'）> ; 0）then ... 
  

和

  if（SQL（'SELECT COUNT（*）FROM table WHERE a = b'）> 1）then ... 
  pre> 
 
 
第一行很容易切换到 EXISTS 语句，但我可以让第二行更多高效？从评论和我自己的想法，我有以下想法，他们是否会更有效率？
  if（SQLRecordCount SELECT TOP 2 1 FROM table WHERE a = b'）> 1）then ... 
  
我可以使用 ROWNUM  for Oracle。）
  if（SQL SELECT 1 FROM table WHERE a = b HAVING COUNT（*）> 1'）= 1）then ... 
  
 
 $ b b 
以下不在SQL Server中工作：
  SELECT COUNT（*）FROM 2 FROM table WHERE a = b）
  
但这与Oracle有关：
  SELECT COUNT（*）FROM（SELECT 1 FROM table WHERE a = b AND ROWNUM <3）
  / pre> 
 
 
感谢您迄今为止的所有帮助。
解决方案
如果索引
 
 
 
示例：
 
 
 
 200万行表，相当宽，900MB 
 
 
 
这将产生17,876行
  SELECT COUNT（*），ThingID FROM dbo.TwoMillion IT GROUP BY ThingID HAVING COUNT（*）> 
 | --Compute Scalar（DEFINE：（[Expr1002] = CONVERT_IMPLICIT（int，[Expr1005]））
 
 | --Filter（WHERE：（[Expr1002]>（1） ]，0）））
 | --Hash Match（Aggregate，HASH：（[IT]。[ThingID]）DEFINE：（[Expr1005] = COUNT（*）））
 | --Index扫描（OBJECT：（[MyDB]。[dbo]。[TwoMillion]。[IX_Thing] AS [IT]））
 
表'Worktable'。扫描计数0，逻辑读取0，物理读取0，预读读0，lob逻辑读0，lob物理读0，lob预读读0 
表'TwoMillion'。扫描计数1，逻辑读取8973，物理读取3，预读读取8969 ...全部为零
  
第二次运行
 表'工作台'。 = same 
表'TwoMillion'。扫描计数1，逻辑读数8973，...全部为零
 
 CPU时间= 453 ms，经过时间= 564 ms。 
  
 
if you want to know if COUNT(*) > 0 then you can use EXISTS to make the query more efficient. Is there a way I can make a query more efficient when I want to know if COUNT(*) > 1?

(Needs to be compatible with both SQL Server and Oracle.)

Thanks, Jamie

Edit:

I am trying to improve the performance of some code. There are some lines similar to:
if (SQL('SELECT COUNT(*) FROM table WHERE a = b') > 0) then...
and
if (SQL('SELECT COUNT(*) FROM table WHERE a = b') > 1) then...
The first line is easy enough to switch to an EXISTS statement, but can I make the second line more efficient? From the comments and my own thoughts I have the following ideas, would any of them be more efficient?
if (SQLRecordCount('SELECT TOP 2 1 FROM table WHERE a = b') > 1) then...
(I can use ROWNUM for Oracle.)
if (SQL('SELECT 1 FROM table WHERE a = b HAVING COUNT(*) > 1') = 1) then...
The following doesn't doesn't work in SQL Server:
SELECT COUNT(*) FROM (SELECT TOP 2 FROM table WHERE a = b)
But this does with Oracle:
SELECT COUNT(*) FROM (SELECT 1 FROM table WHERE a = b AND ROWNUM < 3)
Thanks for all your help so far.
解决方案
It shouldn't matter too much if indexed

Example:

2 million row table, quite wide, 900MB on disk, virtual SQL Server 2005.

This gives 17,876 rows
SELECT COUNT(*), ThingID FROM dbo.TwoMillion IT GROUP BY ThingID HAVING COUNT(*) > 1

  |--Filter(WHERE:([Expr1002]>(1)))
       |--Compute Scalar(DEFINE:([Expr1002]=CONVERT_IMPLICIT(int,[Expr1005],0)))
            |--Hash Match(Aggregate, HASH:([IT].[ThingID]) DEFINE:([Expr1005]=COUNT(*)))
                 |--Index Scan(OBJECT:([MyDB].[dbo].[TwoMillion].[IX_Thing] AS [IT]))

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0
Table 'TwoMillion'. Scan count 1, logical reads 8973, physical reads 3, read-ahead reads 8969... all zeroes
On 2nd run
 Table 'Worktable'. = same
 Table 'TwoMillion'. Scan count 1, logical reads 8973, ... all zeroes

 CPU time = 453 ms,  elapsed time = 564 ms.


                        
这篇关于SQL：使COUNT（*）&gt; 1高​​效的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！