Recursive Query as a View

Question

Recursive Query as a View

alorenzini

SSCarpal Tunnel

Points: 4620
More actions
January 31, 2008 at 11:06 am

#378234

Is there a way to move the following Recursive Query and make it a View:
With downline (ConsultantID,EffectiveDate, ConsultantName,SponsorID,SponsorName,DownLineLevel,
ConsultantXId,SponsorXID,Active, DeactivationDate,BumpupDate,NACDate,CurrentLevelAchieveDate, CurrentLevelXID, StatusID)
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.NACDate
,A.CurrentLevelAchieveDate
,A.CurrentLevelXID
,A.StatusID
FROM dbo.uvwConsultantDownLine A with (nolock)
WHERE A.ConsultantID = @ConsultantID
AND @MonthEndDt Between a.EffectiveDate and A.EffectiveEndDate
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.NACDate
,A.CurrentLevelAchieveDate
,A.CurrentLevelXID
,A.StatusID
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
The reason is that we have multiple SPs that will be using the Recursive Logic and wanted to standardize it so we would not have to re-create it for each proc.
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 1

With hierarchies like this - I'd advocate using some kind of "expansion table" which permanently stores the downlines (current and past if need be). Assuming that downline table doesn't change all that often, you could save yourself a LOT of time (especially if you actually index the expansion table).

Even if you rebuilt the thing a few times a day - if you use this a fair amount like you seem to mention, the off-line process will "pay for itself" in no time flat with better responsiveness elsewhere.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jeff Moden SSC Guru Points: 1004364 More actions · Answer 2

I've gotta agree with that!

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

alorenzini SSCarpal Tunnel Points: 4620 More actions · Answer 3

The only issue with this is the Downline (Heirarchical Tree) is updated every 30 minutes and this could be a pretty big impact on performance when trying to refresh the tree.

Thanks,
Art
Database Analyst
Tastefully Simple, Inc.

alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 4

I'd ask you then this - how often do you see it being used in that 30 minutes? you were talking about referencing it a bunch of times in various other places. If you don't store it - you will be regenerating it each time it's needed. Ultimately - storing it is just a little more work than running just once "into memory" (due to the indexing effort): if you plan on using this info 20 times in a 30-minute timeframe - you've saved yourself having to generate this from scratch, say, 18 times over. The other times are flying through....

As to it being refreshed - you might also consider if "partial" rebuilds would work. I don't know how many things change and how fast - but in something being called downline - I'd have to imagine most changes are happening in the "lower levels". If you can tell what has changed in the last thrity minutes - the only rebuild needed would be whatever is affected by those very changes.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?