Improving Speed of SQL (index spool)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Improving Speed of SQL (index spool)

Author

Topic

mantic
Starting Member

2 Posts

Posted - 2014-10-28 : 10:37:01

Hi, first post so apologies if this is in the wrong area!

I need help with a query which seems to be responsible for slow load times of the report which is fed by it. I have looked at teh actual execution plan and 45% of the cost is being spent on an "index spool". I pinned the SQL down a bit and reckon that withing this snippet lies the problem - specifically the nature of the third inner join (O_STATUS) which is using a rather more complicated join that usual.

Does anyone have a better way of writing the SQL than the one below? Apologies again if this is in the wrong forum, i'm just looking for help!

Thanks!

SELECT ORDERS.WORKSORDER_ID
FROM Repairs_Works_Orders_T (nolock) ORDERS
Inner Join dbo.Repairs_Works_Order_Lines_T O_LINES (nolock)
On ORDERS.WorksOrder_ID = O_LINES.WorksOrder_ID
Inner Join dbo.Repairs_Requests_Task_Lines_T T_LINES (nolock)
On T_LINES.RequestLine_id = O_LINES.RequestTaskLine_id

Inner Join dbo.Repairs_Request_Order_Status_T O_STATUS (nolock)
On ORDERS.WorksOrder_ID = O_STATUS.Source_ID
And O_STATUS.Key_ID = (Select Top 1 Key_ID From Repairs_Request_Order_Status_T O_STATUS_2 (NOLOCK) Where O_STATUS_2.Source_ID = ORDERS.WorksOrder_ID And O_STATUS_2.Status_ID = 11)

Group By ORDERS.WorksOrder_ID

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-28 : 10:50:55

Your inner query in the last join:


Select Top 1 Key_ID From Repairs_Request_Order_Status_T O_STATUS_2 (NOLOCK) Where O_STATUS_2.Source_ID = ORDERS.WorksOrder_ID And O_STATUS_2.Status_ID = 11

has no order by clause. that means that the subquery is not deterministic. You may see a different Key_ID for each execution. Note that this is not a performance problem per se, but a logic problem that may render the report results useless.

FWIW I'd pull that subquery out of the join criteria and add it to a WHERE clause instead (and add an ORDER BY clause). Alternatively, add another JOIN:


Inner Join dbo.Repairs_Request_Order_Status_T O_STATUS  
On ORDERS.WorksOrder_ID = O_STATUS.Source_ID 
Inner JOin (
     Select Top 1 Key_ID 
     From Repairs_Request_Order_Status_T O_STATUS_2
     Where O_STATUS_2.Source_ID = ORDERS.WorksOrder_ID And O_STATUS_2.Status_ID = 11
     ORDER BY .....) q
  on O_STATUS.Key_ID = q.Key_ID

And....please don't use NOLOCK. Use READPAST if you must, but not NOLOCK (dirty reads). READPAST = NOLOCK - dirty reads

mantic
Starting Member

2 Posts

Posted - 2014-10-28 : 11:04:03

Thank you GBRITTON, I will try and apply all the suggestions in your reply. Thanks for the quick response!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources