Query running very long time...

Last Post 12 Mar 2013 05:17 AM by spiral98. 3 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
Muneer Pappattu
New Member
New Member

--
27 Feb 2013 07:34 PM
This query is taking very long time...pls help me to redesign the query if there are any issues with this query

Insert into SituationAnalysisReportNew (InstanceID,MainDepotCode,ItemCode,NSVTY,NSLTY,WVTY,WLTY,CVTY,GSLTY,NSVSTD) Select 1787511, D.MainDepotCode,ItemCode,Sum(((Ind.SalesQty*Ind.SalesPrice)-((Ind.ExpiryQty+Ind.DamageQty+Ind.rejectsQty+Ind.BuyBacksQty)*Ind.ReturnPrice))) NetSalesValue, Sum((Ind.SalesQty-Ind.ExpiryQty-Ind.DamageQty-Ind.rejectsQty-Ind.BuyBacksQty)*IM.LiterPerUnit)NetSalesLiter,sum((Ind.ExpiryQty+Ind.DamageQty+Ind.rejectsQty)*Ind.ReturnPrice) WVTY,Sum((Ind.ExpiryQty+Ind.DamageQty+Ind.rejectsQty)*IM.LiterPerUnit) WLiter, sum((((Ind.SalesQty-Ind.ExpiryQty-Ind.DamageQty-Ind.rejectsQty-Ind.BuyBacksQty)+(Ind.ExpiryQty+Ind.DamageQty+Ind.rejectsQty)+Ind.DiscountQty+Ind.PromotionQty)*Ind.StdCost)) GrossCost,sum((Ind.SalesQty-Ind.BuyBacksQty)*IM.LiterPerUnit) GSL, Sum(((Ind.SalesQty*Ind.STDPrice)-((Ind.ExpiryQty+Ind.DamageQty+Ind.rejectsQty+Ind.BuyBacksQty)*Ind.STDReturnPrice))) STDNetSalesValue From InvoiceHeader IH WITH (NOLOCK),InvoiceDetail InD WITH (NOLOCK),MainStreet MS WITH (NOLOCK),Area A WITH (NOLOCK),Depot D WITH (NOLOCK),ItemMasterGlobal IM WITH (NOLOCK) Where IH.InvNumber=InD.InvNumber And IH.RouteCode = MS.MStreetCode and MS.AreaCode=A.AreaCode and A.DepotCode=D.DepotCode and D.MainDepotCode=12 and ( IH.InvDate Between '21/02/2013' and '27/02/2013') and ItemCode=IM.ActualItemCode and IM.ItemType=1 Group By D.MainDepotCode,ItemCode

rm
New Member
New Member

--
28 Feb 2013 04:41 AM
Did you check execution plan?
gunneyk
New Member
New Member

--
01 Mar 2013 08:42 AM
Like rm stated I would look at the estimated query plan first. But I would also highly recommend you rewrite the query usinghte newer ANSI join syntax that looks like this:
FROM Table1 AS A INNER JOIN Table2 AS b ON A.x = B.x ...
It appears from a quick glance you are missing a join condition which is easy to do with the old style join syntax that puts the conditions in the WHERE clause. If tha tis true you have a cartesien join.
spiral98
New Member
New Member

--
12 Mar 2013 05:17 AM
The only thing that you need is profiling.
You need to have an idea about memory, input/output and processor. You need to know which of these 3 is causing the server to slow down. There are a lot of products that does it (there is even an okay performance monitor that comes with windows installed).

In addition to running profiler and checking also for page life expectancy and also buffer cache hit ratio
You are not authorized to post a reply.

Acceptable Use Policy