Resolving a performance issue with BETWEEN join - eager spool

Question

This query (anonymized) takes about 2 minutes to execute.

SELECT  
    ly.Col1 
    ,sr.Col2
    ,sr.Col3
    ,sr.Col4
INTO TempDb..TempLYT
FROM Tempdb..T1 ly
JOIN TempDb..T2 sr on sr.[DateTimeCol] BETWEEN ly.DateTimeStart and ly.DateTimeEnd
WHERE sr.Col5 = 1 OR sr.Col5 = 2

Are there some alternatives that would help with this query?

After applying the index suggested by Paul White, the query plan looks like this:

Paul White · Accepted Answer · 2018-12-29T10:15:38.907

Ensure you have an index on T1 like:

CREATE INDEX i ON T1 (dtstart, dtend) INCLUDE (col1);

You might reverse the key columns depending on which is more selective with your data set.

The optimizer tends to introduce an eager spool for one of the inequalities and leave the second one in a Filter. Repro:

CREATE TABLE #T1 (col1 integer, dtstart datetime, dtend datetime);
CREATE TABLE #T2 (col2 integer, col3 integer, col4 integer, col5 integer, dtcol datetime);

UPDATE STATISTICS #T1 WITH ROWCOUNT = 5000;
UPDATE STATISTICS #T2 WITH ROWCOUNT = 100000;

SELECT
    T1.col1,
    T2.col2,
    T2.col3,
    T2.col4
FROM #T1 AS T1
JOIN #T2 AS T2 ON T2.dtcol BETWEEN T1.dtstart AND T1.dtend
WHERE T2.col5 IN (1, 2)
OPTION (QUERYTRACEON 8690); -- No perf spool