Slow “Having” Query? Try Pre-Filtering.

 dans Sites anglophones  étiqueté t-sql par

I was helping a client with a query, and I’m going to rework the example to use the Stack Overflow database for easier storytelling.

Say we need to:

Find all the locations where users have logged in since a certain date, then Return the total count of people who live in those locations

One way to write the query would be:

SELECT Location, COUNT(*) AS recs FROM dbo.Users GROUP BY Location HAVING MAX(LastAccessDate) > ‘2022-01-01’ ORDER BY Location;

And in order to expedited it, I’ve added a couple of indexes:

CREATE INDEX Location ON dbo.Users(Location) INCLUDE (LastAccessDate); GO CREATE INDEX