-
alorenzini
  - Joined on 12-12-2007
- Posts 1
|
![Sleep [|-)]](/Community/emoticons/emotion-20.gif)
Performance Tuning a Recursive Query
I have the following script:
DECLARE @ConsultantID AS nVarChar(50)
,@PeriodDate AS DateTime
SET @ConsultantID = '0000344'
SET @PeriodDate = '12-01-2007'
-- Declare Local Variables Declare @MonthStartDt As DateTime
DECLARE @MonthEndDt AS DateTimeDECLARE @QuarterStartDt AS DateTime
DECLARE @QuarterEndDt AS DateTimeDeclare @Year AS DateTime
Declare @PeriodStartDt As DateTime
Declare @PeriodEndDt as DateTime
-- Breakdown @PeriodDate into Quarter and Months
SET @QuarterStartDt = DATEADD(quarter, DATEDIFF(quarter, 0, @periodDate), 0)
Set @QuarterEndDt = DATEADD(quarter, DATEDIFF(quarter, -1, @PeriodDate), -1)
SET @Year = DATEADD(yy, DATEDIFF(yy,0,@PeriodStartDt), 0)
SET @MonthStartDt = DATEADD(month, DATEDIFF(month, 0, @PeriodDate), 0) Set @MonthEndDt = DATEADD(month, DATEDIFF(month, -1, @PeriodDate), -1)
IF @MonthEndDt > GETDATE()
BEGIN
SET @MonthEndDt = GETDATE()END;
-- Declare the Downline Recursive Query
With downline (ConsultantID,EffectiveDate, ConsultantName,SponsorID,SponsorName,DownLineLevel,
ConsultantXId ,SponsorXID,Active, DeactivationDate,BumpupDate,CurrentLevelAchieveDate, CurrentLevelXID )
AS
(
-- Anchor member defintion SELECT A.ConsultantID
,A.EffectiveDate,A.FirstName + ' ' + A.LastName as ConsultantName
,CAST(A.SponsorID AS Nvarchar(MAX)),'' As SponsorName
,0 as DownLineLevel ,A.ConsultantXID
,A.SponsorXID,A.Active
,A.DeactivationDate,A.BumpupDate
,A.CurrentLevelAchieveDate
,A.CurrentLevelXID
FROM dbo.uvwConsultantDownLine A with (nolock)
WHERE A.ConsultantID = @ConsultantID
AND @MonthEndDt Between a.EffectiveDate and A.EffectiveEndDate -- 1 Record
UNION ALL
--Recursive member definition SELECT A.ConsultantID
,A.EffectiveDate,A.FirstName + ' ' + A.LastName as ConsultantName
,cast(A.SponsorID as nvarchar(MAX)) AS SponsorID,'' AS SponsorName
,DownLineLevel + 1 ,A.ConsultantXID
,A.SponsorXID,A.Active
,A.DeactivationDate,A.BumpupDate
,A.CurrentLevelAchieveDate
,A.CurrentLevelXID
FROM dbo.uvwConsultantDownLine AS A with (nolock)
INNER JOIN DownLine AS B ON
A .SponsorID = B.ConsultantID
WHERE @MonthEndDt Between a.EffectiveDate and A.EffectiveEndDate) -- Appropriate records
-- Create the Temp table #Downline that returns the CTE results SELECT A.DownLineLevel
-- ,C.BumpUpDate,A.ConsultantID
,A.EffectiveDate,UPPER(RTRIM(A.ConsultantName)) AS ConsultantName
-- ,C.EmailAddress,D.Title AS AchievedTitle
, CONVERT(NVARCHAR(MAX),A.SponsorID) AS SponsorID,A.SponsorName
,A.ConsultantXID,A.SponsorXID
,A.Active,A.DeactivationDate
,A.BumpupDate,A.CurrentLevelAchieveDate
,A.CurrentLevelXID
INTO #Downline
FROM DownLine AS A with (noLock)
LEFT OUTER JOIN SharedDimension.dbo.DimConsultantTitle AS D ON
A .CurrentLevelXID = D.XID
WHERE DownLineLevel <= 4GROUP BY A.ConsultantID
,A.EffectiveDate,A.ConsultantName
,A.SponsorID,A.SponsorName
,DownLineLevel-- ,C.BumpUpDate
-- ,C.EmailAddress ,D.Title
,A.ConsultantXID,A.SponsorXID
,A.Active,A.DeactivationDate
,A.BumpupDate,A.CurrentLevelAchieveDate
,A.CurrentLevelXID
Select D.ConsultantID ,D.Downlinelevel
,D.ConsultantName,D.SponsorName
,D.EffectiveDate,D.SponsorID
,D.AchievedTitle,ISNULL(CONVERT(CHAR(10),D.CurrentLevelAchieveDate,101),'') AS AchieveDate
,ISNULL(Convert(Char(10),D.BumpupDate,101),'') as BumpupDate
,ISNULL(Sum(CaseWHEN O.OrderCreateDate Between @QuarterStartDt And @QuarterEndDt THEN O.PartOneTotal
ELSE 0END),0) AS QuarterToDate_total
,ISNULL(Sum(CaseWHEN O.OrderCreateDate Between @MonthStartDt And @MonthEndDt THEN O.PartOneTotal
ELSE 0END),0) AS MonthToDate_Total
,D.ConsultantXID,D.SponsorXID
,ISNULL((SELECT ISNULL(SUM(v.PartOneTotal),0) FROM uvw_DownlineOrder V
INNER JOIN SharedDimension.dbo.DimOrderType AS T ONV.OrderTypeXID = T.XID
WHERE (T.OrderType NOT In ('Credit'))AND D.ConsultantID = V.ConsultantID
AND V.OrderCreateDate Between @QuarterStartDt And @QuarterEndDt),0) AS QuarterToDate_Volume,ISNULL((SELECT ISNULL(SUM(v.PartOneTotal),0) FROM uvw_DownlineOrder V
INNER JOIN SharedDimension.dbo.DimOrderType AS T ONV.OrderTypeXID = T.XID
WHERE (T.OrderType NOT In ('Credit'))AND D.ConsultantID = V.ConsultantID
AND V.OrderCreateDate Between @MonthStartDt And @MonthEndDt),0) AS MonthToDate_Volume
,D.Active
-- ,ISNULL((SELECT DISTINCT repFlag FROM dbo.udfGetRepromotes (@ConsultantID) r WHERE repFlag ='X'
-- AND d.ConsultantID = r.Consultantid ),' ') AS RepFlag ,ISNULL(r.RepFlag,' ') AS RepFlag
from #Downline D with (nolock)
LEFT OUTER JOIN uvw_DownlineOrder O ON D.ConsultantID = O.ConsultantID
LEFT Outer JOIN Repromotes r ON d.ConsultantID = r.ConsultantID AND r.repflag = 'X'
WHERE (D.Active = 1) OR (D.DeactivationDate BETWEEN @MonthStartDt AND @MonthEndDt)
AND r.AchieveLevel >= 4GROUP BY D.ConsultantID ,D.Downlinelevel
,D.ConsultantName,D.SponsorName
,D.EffectiveDate,D.SponsorID
,D.AchievedTitle,D.CurrentLevelAchieveDate
,D.BumpupDate ,D.ConsultantXID
,D.SponsorXID,D.Active
,r.RepFlag
-- Drop the temp table
DROP TABLE #Downline
It does what it suppose to but is there things I can look at to speed it up.
Thanks Art
|
|