Wednesday, 17 June 2009

Optimisation in Procedure using Union All Instead of Outer Join

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