Link to home
Start Free TrialLog in
Avatar of programmerist 1983
programmerist 1983Flag for Türkiye

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?

 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

Open in new window

Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

Hi,
Pls try this..

DECLARE @Start DATETIME 
DECLARE @End DATETIME 

SELECT @Start = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) 
SELECT @End = DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE()), -1)

;WITH CTE AS
(
	SELECT p.*,q.SapContractNumber FROM 
	(
		SELECT 
		s.MaterialNumber, 
		s.SapDeliveryNumber,
		sum(s.Stock) Stock,
		s.BillTo,
		s.Quantity
		FROM Stok s
		GROUP BY s.MaterialNumber, s.SapDeliveryNumber, s.BillTo, s.Quantity
	)p
	CROSS APPLY
	(
		SELECT dbo.getSapContractNumberBySapDeliveryNumber(p.SapDeliveryNumber) SapContractNumber
	)q
)
,CTE1 AS
(
	SELECT COUNT(1) FROM Orders o 
	WHERE o.CustomerId = e.CustomerID 
	AND o.SapContractNo = e.SapContractNumber 
	AND o.OrderDate BETWEEN @Start AND @End
)
SELECT 
e.SapContractNumber +'' +e.CustomerId Id,
e.SapContractNumber,
c.ContractName,
e.CustomerId,
cu.CustomerFullName,
e.ortalamastok,
k.cnt OrdersCount
FROM  EmniyetStok e 
left JOIN CTE t ON t.SapContractNumber = e.SapContractNumber
LEFT JOIN Customers cu ON cu.CustomerID = e.CustomerID
LEFT JOIN Contracts c ON c.SAPContractNumber = e.SapContractNumber
OUTER APPLY
(
	SELECT COUNT(*) cnt 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)
)k
WHERE {0} AND e.ortalamastok > 0

Open in new window


Hope it helps!
Avatar of Éric Moreau
enable the "execution plan" and dig into it. You will discover where the time is spent.
ASKER CERTIFIED SOLUTION
Avatar of Chris Luttrell
Chris Luttrell
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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?