I'm working on a complicated query but I'm at a loss as to why it is so slow.
As a table value function * (https://pastebin.com/CjN2sEkd) it takes ~1 minute to run. It uses CTEs to group the data.
As a stored procedure (https://pastebin.com/2UzJu6Hg) it takes ~4 seconds to run. It uses temporary tables to group the data.
The logic & the date range does not change whatsoever between the two.
I suppose I could just go the dumb route that works & switch this over to a USP. My life would not be any different & I would have a working solution. But I was hoping someone could point me to some mistake that I've made so that I can have it as a TVF (which I prefer). Or if there is a known limitation that I should also become aware of.