As suggested by PIEBALDconsult replace the
WHERE
with a join
e.g.
LEFT join Last_Authorised_Range lar on lar.Range_Event_Id = srg.Range_Event_Id
WHERE lar.Range_Event_Id is null
Indexes on the ID fields in the tables should also help.
As Member 10454138 points out, there is no major benefit in having an index on
Match_Flag
if there are only two values
[EDIT] In regard to my last point on the column that only has two values - @RichardDeeming made an excellent point in a comment above. If you are
always going to search for
Match_Flag='Y'
then using a Filtered Index
will bring performance improvement - reference
Introduction to SQL Server Filtered Indexes[
^]