Optimize Query,Taking Time in Executing

Analyst_SQL 3,551 Reputation points

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  
Select '',Sum(orderqty),Sum(Pending),sum(Prdqty),Sum(PK_QTY)  from Cte_F  
order by Descriptionitem asc  
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,080 questions
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,579 questions
0 comments No comments
{count} votes

Accepted answer
  1. LiHongMSFT-4306 24,361 Reputation points

    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

1 additional answer

Sort by: Most helpful
  1. Erland Sommarskog 103.5K Reputation points MVP

    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.

    0 comments No comments