question

Griffindor-0198 avatar image
0 Votes"
Griffindor-0198 asked ·

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
73883-execution-plan.png
73819-details.png


sql-server-generalsql-server-transact-sql
execution-plan.png (71.6 KiB)
details.png (23.7 KiB)
· 4
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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.

0 Votes 0 ·

Hi @Griffindor-0198,

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.

Echo

0 Votes 0 ·

Do you have any update?

0 Votes 0 ·

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..

0 Votes 0 ·

0 Answers