October 3, 2018 at 2:27 pm
rjp123 - Wednesday, October 3, 2018 2:24 PMLynn Pettis - Wednesday, October 3, 2018 2:08 PMSo, tell us again what problem we are trying to solve?Hi Lynn,
Its about performance, checking what is the best possible way to achieve this.
its around 2M row I am trying to deal with, how long ideally it should take?
To query 9 months of data @startdate and @Enddate for 1787 rows it is taking 4 min 9 sec to return results to aggregate and do above calculations.
sometimes if I run again its little faster but its too long time !!!
I would like to see the execution plan for my query with and without the index I suggested above.
October 3, 2018 at 2:30 pm
Part of your problem is the multiple subqueries in your SELECT column list. Each of those are a separately run query.
I am also assuming that the 1787 number of rows of data is from the Customer table. How many rows of data are being processed from the SalesInvoice table from the beginning of last year through the end of September this year?
October 3, 2018 at 3:41 pm
I suggest just this. If that still performs poorly, we'll have to look at the view itself and tune it.
DECLARE @StartDate date = '01-01-2018'
, @EndDate date = '09-30-2018' ;
SELECT C.No_ as [No_], C.Name, C.Spcode, C.Address, C.City, C.[PostCode], C.County,
D.dcValue,
SI.*
FROM dbo.customer C
INNER JOIN (
SELECT billtocust,
SUM(CASE WHEN ShipDate >= @StartDate
THEN SalesAmount ELSE 0 END) AS YTDSales,
SUM(CASE WHEN ShipDate >= @StartDate
THEN SalesAmount - ItemAVGCost * Quantity ELSE 0 END) AS YTDGP,
SUM(CASE WHEN ShipDate <= DATEADD(YEAR, -1, @EndDate)
THEN SalesAmount ELSE 0 END) AS PrevYTDSales,
SUM(CASE WHEN ShipDate <= DATEADD(YEAR, -1, @EndDate)
THEN SalesAmount - ItemAVGCost * Quantity ELSE 0 END) AS PrevYTDGP,
SUM(CASE WHEN ShipDate < @StartDate
THEN SalesAmount ELSE 0 END) AS PrevEOYSales,
SUM(CASE WHEN ShipDate < @StartDate
THEN SalesAmount - ItemAVGCost * Quantity ELSE 0 END) AS PrevEOYGP
FROM dbo.vwSalesInvoice
WHERE ShipDate between DATEADD(YEAR, -1, @StartDate) and @EndDate
GROUP BY billtocust
) AS SI ON SI.billtocust = C.No_
LEFT JOIN vwCustdim D ON C.No_ = D.No_
WHERE (C.Dealer = 1) AND ((C.MC = 1) OR (C.FC = 1))
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 4, 2018 at 8:12 am
Try this:declare @StartDate date = '01-01-2018',
@EndDate date = '09-30-2018'
;
SELECT C.No_ AS [No_],
C.Name, C.Spcode, C.Address, C.City, C.[PostCode], C.County, D.dcValue
INTO #Tmp
FROM customer AS C
LEFT JOIN vwCustdim D
ON C.No_ = D.No_
WHERE C.Dealer = 1
AND (C.MC = 1
OR C.FC = 1)
;
WITH Sales_Breakout_CTE AS
(
SELECT billtocust,
CASE WHEN I.ShipDate between @StartDate and @EndDate
THEN 'YTD'
WHEN I.ShipDate between DATEADD(year, - 1, @StartDate) AND DATEADD(year, - 1, @EndDate)
THEN 'PriorYTD'
ELSE
'Previous'
END AS RecType,
SalesAmount AS YTDSales,
SalesAmount - ItemAVGCost * Quantity AS YTDGP
from vwSalesInvoice I
Where I.ShipDate between DATEADD(year, - 1, @StartDate) and @EndDate
Group by billtocust
)
SELECT billtocust, RecType,
SUM(YTDSales) AS YTDSales,
SUM(YTDGP) AS YTDGP
INTO #TmpYTD
FROM Sales_Breakout_CTE
GROUP BY billtocust, RecType
;
Select T.[No_],
T.Name, T.Spcode, T.Address, T.City, T.[PostCode], T.County, T.dcValue,
CASE WHEN Y.RecType = 'YTD'
THEN YTDSales
ELSE
0
END AS YTDSales,
CASE WHEN Y.RecType = 'YTD'
THEN YTDGP
ELSE
0
END AS YTDGP,
CASE WHEN Y.RecType = 'PriorYTD'
THEN YTDSales
ELSE
0
END AS PrevYTDSales,
CASE WHEN Y.RecType = 'PriorYTD'
THEN YTDGP
ELSE
0
END AS PrevYTDGP,
CASE WHEN Y.RecType = 'Previous'
THEN YTDSales
ELSE
0
END AS PrevEOYSales,
CASE WHEN Y.RecType = 'Previous'
THEN YTDGP
ELSE
0
END AS PrevEOYGP
from #Tmp AS T
Left Join #TmpYTD AS Y
on Y.billtocust = T.[No_]
;
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
Viewing 4 posts - 31 through 34 (of 34 total)
You must be logged in to reply to this topic. Login to reply