Optimize Query,Taking Time in Executing

Question

I have below query ,which is taking 8 second in executing, so what should i do to reduce executing time,

with CTE_I as (  
  
Select CodeItem,Descriptionitem from ItemMasterFile ),  
Cte_S as (Select CodeItem,S.Order_Ref_No,orderqty,S.OrderNo from SalesOrder S inner join OrderDetail D on S.OrderNo=D.OrderNO   
where S.Status='Open' and d.Del_ID is null),  
  
Cte_P as (Select Codeitem,sum(prdqty) Prdqty from Probale  
  
where DelID is null and OrderNo=17576  
group by Codeitem ),  
  
Cte_Pk as ( select D.Codeitem,M.OrderNo,sum(D.qty)PK_QTY  from tbl_BalPacM M   
                                              inner join tbl_PckDetail D on M.PID=D.PID   
											  where D.DelID is null and M.Del is null and M.OrderNo=17576  
											  Group by D.Codeitem,M.OrderNo  
											  )  
											  ,Cte_F as (  
select Descriptionitem,orderqty,Prdqty,PK_QTY ,isnull(Orderqty-Prdqty,0) Pending from  cte_I I left join Cte_S O on O.CodeItem=I.Codeitem  
                                              left join Cte_P P on P.codeitem=I.Codeitem  
											  left join Cte_Pk PK on PK.Codeitem=I.CodeItem  
where O.OrderNo=17576)  
  
Select Descriptionitem,orderqty,Pending,Prdqty,PK_QTY  from Cte_F  
UNION  
Select '',Sum(orderqty),Sum(Pending),sum(Prdqty),Sum(PK_QTY)  from Cte_F  
  
  
order by Descriptionitem asc  

Answer 1

Hi @Analyst_SQL
Try the following possible measures:

1. Replace a complex statement with multiple simple statements.
2. Make sure the columns used in filter conditions are indexed.
3. Check the execution plan, if it contains nested loops where there is no index defined, then you might be in trouble. If so, try running the query with Hash Join, Merge Join to avoid nested loop joins, and validate which could be faster.

It would be better if you post your execution plans here, so that we’ll get a right direction.

Best regards,
Li Hong

Answer 2

From the query text alone, there is not much we can say. But I note that you refer to CTE_F twice. This means that CTF_F is likely to be computed twice. Therefore, it would be better to save CTF_F to a temp table and split up the query in two.

Also, replace UNION with UNION ALL. UNION implies DISTINCT, so that will have SQL Server spend cycles on trying remove duplicates.

If these pieces of advice are not correct, we need to see the execution plan. You can upload it to http://www.pastetheplan.com.