SQL query on large tables fast at first then slow

Question

Below query returns the initial result fast and then becomes extremely slow.

SELECT A.Id
, B.Date1
FROM A
LEFT OUTER JOIN B
ON A.Id = B.Id AND A.Flag = 'Y'
AND (B.Date1 IS NOT NULL AND A.Date >= B.Date2 AND A.Date < B.Date1)

Table A has 24 million records and Table B has 500 thousand records.

Index for Table A is on columns: Id and Date

Index for Table B is on columns: Id, Date2, Date1 - Date1 is nullable - index is unique

Frist 11m records are returned quite fast and it then suddenly becomes extremely slow. Execution Plan shows the indexes are used.

However, when I remove condition A.Date < B.Date1, query becomes fast again.

Do you know what should be done to improve the performance? Thanks

UPDATE: I updated the query to show that I need fields of Table B in the result. You might think why I used left join when I have condition "B.Date1 is not null". That's because I have posted the simplified query. My performance issue is even with this simplified version.

Answer 1

You can maybe try using EXISTS. It should be faster as it stops looking for further rows once a match is found unlike JOIN where all the rows will have to be fetched and joined.

select id
from a
where flag = 'Y'
    and exists (
        select 1
        from b
        where a.id = b.id
            and a.date >= b.date2
            and a.date < b.date1
            and date1 is not null
        );