Need help speeding up a stored procedure
ALTER PROCEDURE [dbo].[yearlynetsales]
(
@YearToGet int,
@current datetime
)
AS
SET @YearToGet = 2006;
WITH Years AS (
SELECT DATEPART(year, GETDATE()) [Year]
UNION ALL
SELECT [Year]-1 FROM Years WHERE [Year]>@YearToGet
),
q_00 as (
select
DIVISION
, DYYYY
, sum(PARTY) as asofPAX
, sum(NetAmount) as asofSales
, sum(OtherAmount) as otherSales
from dbo.B101BookingsDetails
INNER JOIN Years ON B101BookingsDetails.DYYYY = Years.Year
where SDESCR not in (select * from dbo.regionexlude) and Booked <= CONVERT(int,DateAdd(year, Years.Year - Year(getdate()), DateAdd(day, DateDiff(day, 0, getdate()), 1)))
and DYYYY = Years.Year and SDESCR not in (select * from dbo.regionexlude) and SDESCR IS NOT NULL
group by DIVISION, DYYYY, years.year
having DYYYY = years.year
),
q_01 as (
select
DIVISION
, DYYYY
, sum(PARTY) as YEPAX
, sum(NetAmount) as YESales
, sum(OtherAmount) as YEotherSales
from dbo.B101BookingsDetails
INNER JOIN Years 开发者_StackOverflow中文版 ON B101BookingsDetails.DYYYY = Years.Year
where SDESCR not in (select * from dbo.regionexlude)
group by DIVISION, DYYYY , years.year
having DYYYY = years.year
),
q_02 as (
select
DIVISION
, DYYYY
, sum(PARTY) as CurrentPAX
, sum(NetAmount) as CurrentSales
, sum(OtherAmount) as CurrentotherSales
from dbo.B101BookingsDetails
INNER JOIN Years ON B101BookingsDetails.DYYYY = Years.Year
where SDESCR not in (select * from dbo.regionexlude) and Booked <= CONVERT(int,@current)
and DYYYY = (year( getdate() )) and SDESCR not in (select * from dbo.regionexlude)
group by DIVISION, DYYYY
)
select
a.DIVISION
, a.DYYYY
, asofPAX
, (asofSales + othersales) as asofSales
, YEPAX
, (YESales + YEothersales) as YESales
, CurrentPAX
, (CurrentSales + Currentothersales) as CurrentSales
,(asofSales + othersales)/ ISNULL(NULLIF((YESales + YEothersales),0),1) as percentsales,
CAST((asofpax) AS DECIMAL(5,1))/yepax as percentpax
from q_00 as a
join q_01 as b on (b.DIVISION = a.DIVISION and b.DYYYY = a.DYYYY)
join q_02 as c on (b.DIVISION = c.DIVISION)
JOIN Years as d on (b.dyyyy = d.year)
where A.DYYYY <> (year( getdate() ))
order by a.DIVISION, a.DYYYY ;
Wow, lots of places to tweak this; let's start with changing the NOTs to LEFT JOIN's with NULLs:
q_01 AS ( SELECT DIVISION
, DYYYY
, SUM(PARTY) AS YEPAX
, SUM(NetAmount) AS YESales
, SUM(OtherAmount) AS YEotherSales
FROM dbo.B101BookingsDetails
INNER JOIN Years ON B101BookingsDetails.DYYYY = Years.Year
LEFT JOIN dbo.regionexlude r ON SDESCR = r.columnname
WHERE r.columnname IS NULL
/*where SDESCR not in (select * from dbo.regionexlude)*/
GROUP BY DIVISION
, DYYYY
, years.year
HAVING DYYYY = years.year ),
Add Set Statistics Profile On before your query. You can then see which parts are taking the longest to execute
SET STATISTICS PROFILE ON
SELECT * FROM [TABLE]
Show Performance Data in SQL server
精彩评论