2

I have a query that first counts the number of rows to be returned and, if it's below a limit, it returns those rows, else it returns nothing.

For context: I have a map that displays some points only after a certain zoom level. However, my users now want that the map displays the points as long as there is space in the screen, instead of having a predefined minimum zoom level. This means that now whenever they move across the map, I'll have two queries, instead of one: one to know how many items there are, and another one to actually return them. The load will potentially double. I thought about doing it this way:

SELECT p.ACOLUMN, p.ANOTHERCOLUMN, p.LATITUDE, p.LONGITUDE 
FROM table p 
where p.LATITUDE < @latMax)
and p.LATITUDE > @latMin) 
and p.LONGITUDE < @lngMax)
and p.LONGITUDE > @lngMin)
and (SELECT count(*) 
FROM table
where LATITUDE < @latMax
and LATITUDE > @latMin
and LONGITUDE < @lngMax
and LONGITUDE > @lngMin)) < 200 -- example limit value

Looking at the actual execution plan, I see that SQL Server 2017 is seeking on the same index twice:

Actual execution plan shows the same nonclustered index seek happening twice

It's also of course reading the same amount of rows in both seeks. Is there a way to remove those duplicate seeks? Or is there maybe another, more optimal way to do what I'm trying?

  • Presumably you can estimate the odds a call will hit the row count threshold vs not? I'd build it optimizing whichever is more likely. Or put Aaron's logic into a SP to remove the extra roundtrip if you're concerned about that. – LowlyDBA Aug 19 at 22:31
  • @LowlyDBA Yes, we could estimate, but that's another story. We would have to build client site logic to do that. Right now we need to deliver this and I wanted to do it in a way that affects performance as little as possible. And I'm sorry but I don't see how Aaron suggestion helps in any way. My query can already avoid the roundtrip. What I want to know is if there's another way that is more performant than the one I presented. – andre_ss6 Aug 19 at 22:59
5

I didn't really mean round trip all the way to the application. I consider a stored procedure one form of "application" in this context.

DECLARE @c int;

SELECT @c = COUNT(*) FROM dbo.table WHERE ...;

IF @c < 200
BEGIN
    SELECT cols FROM dbo.table WHERE ...;
END

If the application always expects either an empty or non-empty resultset, you can do this:

DECLARE @c int;

SELECT @c = COUNT(*) FROM dbo.table WHERE ...;

SELECT TOP (CASE WHEN @c <= 200 THEN @c ELSE 0 END) cols 
  FROM dbo.table WHERE ...;

Yes, you have to read the table twice (in the case where there are fewer than 200 rows; TOP (0) should short-circuit), but I don't see a way to avoid that, unless you maybe dump rows into a temporary structure first (this will be useful I suppose in cases where your I/O is way faster than your network, but you'd still only be writing 201 rows at most, which is probably still way better than moving a lot more rows across the network):

SELECT TOP (201) cols INTO #f FROM dbo.table WHERE ...;

IF @@ROWCOUNT <= 200
BEGIN
  SELECT cols FROM #f;
END

DROP TABLE #f;

You could also try this, but I'm not sure the results will be any better than your original, it all depends if SQL Server sees that it doesn't have to materialize the CTE twice:

;WITH c AS 
(
   SELECT cols, rn = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
   FROM dbo.table WHERE ...
)
SELECT cols FROM c WHERE NOT EXISTS (SELECT 1 FROM c WHERE rn > 200);
  • To minimize the impact to the application it might be better to always return the resultset. Like this: SELECT cols FROM #f WHERE @@ROWCOUNT <= 200 – Alexander Suprun Aug 20 at 4:48
  • @AlexanderSuprun sure, you could always do SELECT TOP (CASE WHEN @@ROWCOUNT <= 200 THEN 200 ELSE 0 END) ... – Aaron Bertrand Aug 20 at 11:40
  • The CTE solution is slower and SP doesn't change anything in relation to my solution besides being a SP (which we don't like to use in our project) and being a little more expressive. By the way, I've tried simply selecting the count both in the outer query and in the subquery (meaning they were both identical except of course for the predicate in the outer query which contains the subquery) and SQL Server still seeked twice. Do you know if it's simply a case of a missing optimization or is there anything else to it? Maybe it thinks the table can change between a seek and another? – andre_ss6 Aug 20 at 20:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.