New Analyst SQL Optimization – Multiple Spools

Unfortunately my ability to Query has outgrown my knowledge of SQL Optimization, so i am hoping someone would help a young analyst by looking at this atrocious execution plan and provide some wisdom as to how i could speed it up. I’ve read a few threads about spooling, but they were all mostly a discussion about weather an Eager Table spool is good or bad, and the answer is always “it depends”.

My execution plan looks like it’s Spooling and Sorting the same #Temp Table multiple times, and it’s eating up a lot of execution cost.

My understanding of a Table Spool is that it is temporary storage to be used later, but if the data is already stored for later use, why would it spool the same data over and over again? My query doesn’t require any ordering so why would it need to sort the same #TempTable/Spool multiple times?

I’m so new to this, i can’t figure out how to attach the entire execution plan…. so i attached an image of the bottom half of it…

Help me experienced analysts. You’re my only hope.

A Little Context.

I currently have a transaction table that tracks all changes made to a lead in my CRM, and i am attempting to create a new table from this data to speed up reporting.

I am pulling data from this transaction table and flagging the first action, first user, and other firsts of a lead by using Row_Number(). I am then inserting every “first” into a #Temp Table, as i know i am going to utilize this data multiple times.

SELECT
ID,
Action,
ROW_NUMBER() OVER (PARTITION BY ID, Action ORDER BY DATE) AS ActionNum,
ROW_NUMBER() OVER (PARTITION BY ID, Actor  ORDER BY DATE) AS USERNUM
INTO #Temp
FROM Table
;

I am then Left joining this #Temp Table many times (10 times actually). I have tried multiple other ways of solving this issue but using Row_Number multiple times seems like the best solution.

SELECT
*
FROM #temp T1
     LEFT JOIN #Temp T2
               ON T2.ID = T1.ID AND T2.Action = A2 AND T2.ActionNum = 1
     LEFT JOIN #Temp T3
               On T3.ID = T1.ID AND T3.Action = A3 AND T3.ActionNum = 1
     LEFT JOIN #Temp T4
               ON T4.ID = T1.ID AND T4.UserNum = 1
WHERE
T1.Action = A1
AND
T1.ActionNum = 1

I’ve looked into creating a clustered index on the #TempTable, but i must not be doing it right because it didn’t change anything about my execution.

Thanks in advance for all your help! Any good reading material is also greatly apprecaited!

Best,
Austin

Have you considered created a permanent table in the / a database with clustered index creation (and appropriate NCIs)? You can then simply query that table and allow for statistics to build up – presumably and only with a very limited understanding of your actual context, if the data is transactional, you can build up that historical data and re-use it for different use cases.
B