I have a script that build some # tables and the result is a
SELECT * query.
This query where I plug in the actual variables used runs in a few seconds
SELECT * FROM #rec_subids INNER JOIN #max_payment_dt ON 1 = 1 INNER JOIN vw_staging_lpmt ON lpmt_subid = 1008 AND lpmt_asofdt >= '2017-09-20'
This query where the variables actually come from the # tables runs in 10+ minutes
select * from #max_payment_dt 2017-09-20 00:00:00.000 select * from #rec_subids 1008
SELECT * FROM #rec_subids INNER JOIN #max_payment_dt ON 1 = 1 INNER JOIN vw_staging_lpmt ON lpmt_subid = r_subid AND lpmt_asofdt >= max_asofdt
Any thoughts of how I can speed up the second query without changing the
These are 2 columns - 1 record, an ID and as Date. I understand the hardcoding would work better but can't hardcode here.
Here is the plan of the poorly performing query.
Here is the plan of the much better performing query.