Why is this query taking so long to execute?
I have a stored procedure that runs this query ...
So, I am passing it a variable (_TerritoryID) to use in a subquery to get a list of postcodes. The subquery returns a list of about 20 or so Distinct postcodes from the subquery. The tbl_owners table has about 12000 owners.
SELECT * FROM tbl_owners WHERE OwnerPostcode IN (SELECT DISTINCT Postcode FROM tbl_postcodes WHERE TerritoryID = _TerritoryID) ORDER BY OwnerLastName;
The query takes a minute or more to run.
If I supply a list of postcodes e.g. ... WHERE OwnerPostcode in ("2144","2156","2142") ... the query is very fast.
This makes me think that maybe the query is running the subquery ever time. Is that the case? And if so, is there a better way to write this query so that it only does the subquery once?