Existing Procedure of Outer Join can also be rewritten in Union All. We have experienced optimisation in this way.
Original Procedure using Joins
CREATE Procedure [dbo].[RevenueReport_V2] (@month int, @year int)
as
Begin
select
isnull(isnull(x.AgentID,y.AgentID),z.AgentID) AgentID,
(select company from DatabaseName.dbo.T_AgentDetails(nolock) where AgentID=isnull(isnull(x.AgentID,y.AgentID),z.AgentID)) Company,
isnull(isnull(x.month,y.month),z.month) [Month],
isnull(isnull(x.year,y.year),z.year) [Year],
isnull(z.Clicks,0) TotalClicks,
isNull(x.RecordsCollected, 0)RecordsCollected,
isNull(y.Delivered,0)Delivered,
isNull(x.Rejected,0) Rejected,
isNull(y.Returned,0) Returned,
isNull(y.ReturnedCredit,0) ReturnedCredit,
isNull(y.PayToAgent,0) PayToAgent,
isNUll(y.Revenue,0) Revenue,
isNull(y.NetRevenue,0) NetRevenue
from
(
select
count(*) RecordsCollected,
AgentID,
Sum(Case when status > 3 then 1 else 0 end) Rejected,
@year [Year],
@month [Month],
(select ProductName from T_Products(nolock) where AgentID=T_ProductCollection.AgentID) ProductName
from T_ProductCollection (nolock)
where AgentID <> 1 and Year(date) = @year and Month(date) = @month
group by AgentID,month(date), year(date)
)x
full outer join
(
select
AgentID,
count(*) Delivered,
Sum(case When RecordPrice <> 0 then RecordPrice else 0 end ) Revenue,
Sum(case When RecordPrice > 0 then RecordPrice else 0 end ) + Sum( case When RecordPrice <> 0 then AgentPrice else 0 end ) PayToAgent,
@year [Year],
@month [Month],
(select ProductName from T_Products(nolock) where AgentID=t_clientleads.AgentID) ProductName
from t_clientleads (nolock)
where AgentID <> 1 and Month(srdate) = @month and Year(srdate) = @year
group by AgentID, month(srdate), year(srdate)
)y
on y.AgentID = x.AgentID
full outer join
(
select
Month(Date) [Month],
Year(Date) [Year],
count(*) Clicks,
AgentID,
(select ProductName from T_Products(nolock) where AgentID=TrackingDB.dbo.T_ProductTracking.AgentID) ProductName
from TrackingDB.DBO.T_ProductTracking(nolock)
where AgentID <> 1 and Year(date) = @year and Month(date) = @month
group by
AgentID,
Month(Date),
Year(Date) )Z on z.AgentID = y.AgentID
and z.AgentID = x.AgentID
End
Rewritten Using Union All:
create PROCEDURE [dbo].[RevenueReport_v3]
(@month int, @year int)
AS
BEGIN
With AgentReport (AgentID, [Month],[Year],[RecordsCollected],[Delivered],[Rejected],[Returned],[ReturnedCredit], [PayToAgent],[Revenue],[TotalClicks]) AS
(
SELECT
AgentID,
MONTH(date) as [Month],
YEAR(date) as [Year],
Count_BIG(*) As [RecordsCollected],
0 AS [Delivered],
sum(case when status>3 then 1 else 0 end) As Rejected,
0 AS [Returned],
0 AS [ReturnedCredit],
0 AS [PayToAgent],
0 AS [Revenue],
0 AS [TotalClicks]
from dbo.T_ProductCollection (nolock)
where AgentID<>1 and Month(date) = @month and Year(date) = @year
--datediff(dd, date,isnull(@StartDate,getdate())) <= 0 and datediff(dd, date, isnull(@EndDate,getdate())) >= 0
Group by AgentID,MONTH(date),Year(date)
UNION ALL
SELECT
AgentID,
MONTH(srdate) as [Month],
YEAR(srdate) as [Year],
0 As [RecordsCollected],
Count_BIG(*) [Delivered],
0 AS Rejected,
sum(case when RecordPrice <> 0 then AgentPrice else 0 end ) [PayToAgent],
sum(case when RecordPrice > 0 then RecordPrice else 0 end ) [Revenue],
0 AS [TotalClicks]
from DBO.T_ProductDelivery (nolock)
where AgentID<>1 and Month(srdate) = @month and Year(srdate) = @year
--datediff(dd, srdate,isnull(@StartDate,getdate())) <= 0 and datediff(dd, srdate, isnull(@EndDate,getdate())) >= 0
Group by AgentID,MONTH(srdate),Year(srdate)
UNION ALL
SELECT
AgentID,
MONTH(date) as [Month],
YEAR(date) as [Year],
0 AS [RecordsCollected],
0 AS [Delivered],
0 AS Rejected,
0 AS [Returned],
0 AS [ReturnedCredit],
0 AS [PayToAgent],
0 AS [Revenue],
count(*) [TotalClicks]
FROM [TrackingDB].[dbo].[T_ProductTracking](nolock)
where AgentID<>1 and Month(date) = @month and Year(date) = @year
--datediff(dd, date,isnull(@StartDate,getdate())) <= 0 and datediff(dd, date, isnull(@EndDate,getdate())) >= 0
Group by AgentID,MONTH(date),Year(date)
)
SELECT
[Rpt].[Month],
[Rpt].[Year],
[Rpt].[AgentID],
[Aff].[ProductName],
SUM([Rpt].[RecordsCollected]) AS [RecordsCollected],
SUM([RPT].[Delivered]) AS [Delivered],
SUM([RPT].[Rejected]) AS [Rejected],
SUM([RPT].[Returned]) AS [Returned],
SUM([RPT].[ReturnedCredit]) AS [ReturnedCredit],
SUM([RPT].[PayToAgent]) AS [PayToAgent],
SUM([RPT].[Revenue]) AS [Revenue],
(SUM([RPT].[Revenue])-SUM([RPT].[ReturnedCredit])) as NetRevenue,
SUM([RPT].[TotalClicks]) AS [TotalClicks]
FROM AgentReport AS [Rpt]
LEFT JOIN DatabaseName.dbo.T_AgentDetails As [Aff](nolock) ON [Aff].AgentID = [Rpt].[AgentID]
--LEFT JOIN T_Products As [Product](nolock) ON [Product].ProductID =[Rpt].ProductID
GROUP BY [Rpt].[AgentID],[Aff].[company],[Rpt].[Month],[Rpt].[Year]
End
No comments:
Post a Comment