December 12, 2007 at 12:15 pm
I have th e following recursive query:
DECLARE@ConsultantIDASnVarChar(50)
,@PeriodDateASDateTime
SET @ConsultantID = '0000344'
SET @PeriodDate = '12-01-2007'
-- Declare Local Variables
Declare @MonthStartDt As DateTime
DECLARE @MonthEndDt AS DateTime
DECLARE @QuarterStartDtASDateTime
DECLARE @QuarterEndDt AS DateTime
Declare @YearASDateTime
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
SELECTA.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
SELECTA.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 <= 4
GROUP 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(Case
WHEN O.OrderCreateDate Between @QuarterStartDt And @QuarterEndDt THEN O.PartOneTotal
ELSE 0
END),0) AS QuarterToDate_total
,ISNULL(Sum(Case
WHEN O.OrderCreateDate Between @MonthStartDt And @MonthEndDt THEN O.PartOneTotal
ELSE 0
END),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 ON
V.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 ON
V.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 >= 4
GROUP 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's suppose to do but it really slow, any tips on how to tune it?
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
December 12, 2007 at 2:46 pm
Before you run the query, click 'Include actual execution plan' and see how it looks like when the query is finished. look for table scans for example, try to eliminate them (if number of rows in table is big) by indexing.
that's for the beginning 😉
Piotr
...and your only reply is slàinte mhath
December 12, 2007 at 3:31 pm
You are so correct, I looked at the query plan and found I was missing few indexes, which I appl;ied and now its a speedy proc.
Thanks
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply