matt.gyton (7/21/2016)
So it turns out my earlier optimism was slightly misplaced....the code change did not provide the fix 🙁However, I noticed that it temporarily improved performance until the SP ran with a specific parameter, then it began performing poorly again. I was slightly surprised, because I had already added OPTION(RECOMPILE) to the main statement in the SP to try and prevent parameter sniffing, which seemed to have worked previously.
I have now recreated the SP using CREATE PROCEDURE...WITH RECOMPILE and it seems to give much more consistent performance results. Not sure why the OPTION(RECOMPILE) was not sufficient, but it looks promising none the less...!
This is interesting. Can you post an example actual plan for a slow and a fast execution?
How many rows in your table variables?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden