programmerist 1983
asked on
how can i make refactoring for below code for making faster?
Hi;
This code is developed by my colleguge. it is really really slow. i need some help to make it fater. i don't want to create any temp table . Can you help me how to make tis query faster?
This code is developed by my colleguge. it is really really slow. i need some help to make it fater. i don't want to create any temp table . Can you help me how to make tis query faster?
SELECT
s.MaterialNumber,
sum(s.Stock) Stock,
dbo.getSapContractNumberBySapDeliveryNumber(s.SapDeliveryNumber) SapContractNumber,
s.BillTo,
s.Quantity
INTO #tmp
FROM Stok s
GROUP BY s.MaterialNumber, s.SapDeliveryNumber, s.BillTo, s.Quantity
SELECT DISTINCT
e.SapContractNumber +'' +e.CustomerId Id,
e.SapContractNumber,
c.ContractName,
e.CustomerId,
cu.CustomerFullName,
e.ortalamastok,
(SELECT COUNT(*) FROM Orders o
WHERE o.CustomerId = e.CustomerID
AND o.SapContractNo = e.SapContractNumber
AND o.OrderDate
BETWEEN DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) AND DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE()), -1)) OrderCount
FROM EmniyetStok e
left JOIN #tmp t ON t.SapContractNumber = e.SapContractNumber
LEFT JOIN Customers cu ON cu.CustomerID = e.CustomerID
LEFT JOIN Contracts c ON c.SAPContractNumber = e.SapContractNumber
where {0} AND e.ortalamastok > 0
DROP TABLE #tmp
enable the "execution plan" and dig into it. You will discover where the time is spent.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
i don't want to create any temp table .Why not?
Can you help me how to make tis query faster?That DISTINCT keyword may be very bad for the query performance. Can you provide the Execution Plan for the query?
Pls try this..
Open in new window
Hope it helps!