MS SQL INNER JOIN is taking very long time [closed]
The below part of the CTE runs very very slow. When I look at the execution plan 78% of its cost goes to Nested Loops (Inner Join)
Are there any issue w.r.t performance in this query ? Below is JOIN that is consuming the memory
SELECT D_HQ.Hour_Quarter_ID, c_AP.PrevEvent, c_AP.PrevEventTime, c_AP.Event, c_AP.Operator_ID, c_AP.Zone_ID, c_AP.Load_Unit_Id FROM cte_Apply c_AP JOIN OA.DIM_Hour_Quarter D_HQ ON c_AP.PrevEventTime >= D_HQ.Hour_Quarter_Start_Time AND c_AP.PrevEventTime < D_HQ.Hour_Quarter_End_Time OPTION (MAXRECURSION 0)
Part of the Execution Plan
It is impossible to assist with a performance problem when we only see part of a query and a graphical plan.
We need to see the full query and the actual execution plan as XML. You can use http://www.brentozar.com/pastetheplan for the latter.
Composing a script with table and index definitions and attach that script can also be a good idea.
Hi @Griffindor ,
Welcome to the Microsoft TSQL Q&A Forum!
As ErlandSommarskog said, you need to provide a complete statement and execution plan. In addition, if there are test data, it would be better.
Do you have any update?
You're filtering a gazillion rows.. what are you doing?
Why is the hour/quarter dimension that large and exploding from the first scan to the index spool? Seems like a predicate push down...
Also the CTE seems to have some number problems..
Sign in to comment