CODEWITHSUNDEEP
sqlsql-server
scc
scc

Reputation: 10696

How to do a SQL count only up to x number?

I want to know whether a given query has more than x elements in a performant way. Let's say I have a query that outputs 2 billion rows but I only want to know if the result set is bigger than 10k, how would I do this without the SQL engine counting up to the 2 billion?

I tried this

SELECT 1 
WHERE EXISTS (SELECT COUNT(1) FROM mytable 
              WHERE somefilter = 58 
              HAVING COUNT(1) < 10000)

but it seems just as slow (or more) than a simple count

SELECT COUNT(1) 
WHERE somefilter = 58

This is for SQL Server 2016.

Any ideas?

Upvotes: 1

Views: 839

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269973

You can do:

select 1
where (select count(*)
       from (select top (10000) 1
             from mytable 
             where somefilter = 58
            ) x
      ) < 10000

The innermost subquery returns at most 10,000 rows. If there are more than 10,000 then the query stops at the first 10,000 and returns 10,000.

Note: If there are fewer than 10,000 rows, then this will not affect performance, because all rows will need to be generated. You may need to add indexes or partitions to really improve performance.

Upvotes: 4

Related Questions