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:
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?