1

I need to check if there are more than x rows in a large database. I came up with this:

SELECT 1 FROM (SELECT NULL AS X FROM [Meta].BannerViews 
WHERE CustomerId='6cc639d6-eb07-49bb-9f8d-e3d0d6f063ae') AS T0 HAVING COUNT(*) > X

But is this the most efficient way to do this? Let's stay COUNT(*) is 100_000+ and X = 2.

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
Niels Bosma
  • 11,758
  • 29
  • 89
  • 148
  • Just use `COUNT(*)` or, since you're on SQL Server, if you can accept an approximate answer, use one of the statistics tricks given in the duplicate answer. – Tim Biegeleisen Aug 30 '22 at 09:42
  • Aside: There is an index on `CustomerId`, right? Another possibility is to denormalize and keep a count of `BannerViews` by `CustomerId`. If this needs to be fast, e.g. handling paid impressions of advertisements on web pages, there may be better ways to manage the data, e.g. keeping the number of impressions remaining and campaign end date. – HABO Aug 30 '22 at 12:51

1 Answers1

2

The fastest way in this particular instance would be to add TOP(@X + 1) to the inner query, this means it would bail out when more than that number is reached

SELECT 1
FROM (
    SELECT TOP (@X + 1) NULL AS X FROM [Meta].BannerViews 
    WHERE CustomerId = '6cc639d6-eb07-49bb-9f8d-e3d0d6f063ae'
) AS T0
HAVING COUNT(*) > @X;

One other method, somewhat more complex too understand, is to use an OFFSET...FETCH clause. You must add an ORDER BY though

SELECT 1
FROM [Meta].BannerViews 
WHERE CustomerId = '6cc639d6-eb07-49bb-9f8d-e3d0d6f063ae'
ORDER BY (SELECT NULL)
OFFSET @X ROWS FETCH NEXT 1 ROW ONLY;

If you want to count the whole table, not just a subset, and don't mind an approximate answer, then use one of the solutions to this question:
Fastest way to count exact number of rows in a very large table?
Those will not work here as you are counting a subset of the table.

Charlieface
  • 52,284
  • 6
  • 19
  • 43