New Analyst SQL Optimization - Multiple Spools
0
votes
1
answer
81
views
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 
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
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
Asked by user147950
(1 rep)
Mar 28, 2018, 10:58 PM
Last activity: Apr 2, 2018, 06:58 PM
Last activity: Apr 2, 2018, 06:58 PM