Querying link records and obtaining downlines by a given level. 1

[junwebhead]

Hi everyone I have a problem in creating a link records with a certain level of downlines.

What I mean is I want to get all the downlines of a customers n levels deep.
I have a customers table that has contain two fields that define their own downlines. first field is the customercode the second is the sponsorcode.

for example I want to get the downlines of customercode 3242
what I will do is search for all other customers with sponsorcode is 3242. That set of records is the customers 1st level downlines. then I will look up the 2nd level downlines by searching for other customers with sponsorcode using the 1st level customercode.

here is my solution so far but this takes 5 minutes to execute on a table with more than 200,000 records. what I want is the best and fastest solution for my problem. Am I using the cursor correctly? What am I missing here.

here is my first stored procedure:

CREATE PROCEDURE dbo.spCustomerLevels
	(
	@CustCode int, @MaxLevel int = 1
	)
AS
	SET NOCOUNT ON
	
	DELETE FROM tbltmpLevel
	DECLARE @Level int, @Counter int, @CurrCode int, @CustomerCode int, @SponsorCode int
	
	SET @Counter = 0
	
	WHILE @Counter <> @MaxLevel
		BEGIN
			IF @Counter = 0
				BEGIN  /* get first level downlines */
					SET @Level = @Counter + 1
					EXECUTE spInsertToLevel @CustCode, @CustCode, @Level
				END
			ELSE
				BEGIN  /* get downlines from second level to maxlevel*/
					SET @Level = @Counter
					DECLARE Customer_Cursors CURSOR FAST_FORWARD FOR
					SELECT CustomerCode, SponsorCode FROM tbltmpLevel
					WHERE TopLevelCode = @CustCode AND CustomerLevel = @Level
					
					OPEN Customer_Cursors
			
					FETCH NEXT FROM Customer_Cursors INTO
					@CustomerCode, @SponsorCode
					
					WHILE @@FETCH_STATUS = 0
						BEGIN
							SET @Level = @Counter + 1
							EXECUTE spInsertToLevel @CustomerCode, @CustCode, @Level
							
							FETCH NEXT FROM Customer_Cursors INTO
							@CustomerCode, @SponsorCode
						END
					
					CLOSE Customer_Cursors
					DEALLOCATE Customer_Cursors
				END
			
			SET @Counter = @Counter + 1
		END
RETURN


GO

Here is the spInsertToLevel that was called above inside the loop

CREATE PROCEDURE dbo.spInsertToLevel
(
	@CustCode int, @TopLevelCode int, @Level int = 1
)
AS
SET NOCOUNT ON
	INSERT INTO tbltmpLevel(CustomerCode, SponsorCode, CustomerLevel, TopLevelCode) 
	
	SELECT CustomerCode, SponsorCode, @Level, @TopLevelCode 
	FROM Customers WHERE SponsorCode = @CustCode AND CustLevel = 'DC'
RETURN


GO

Thanks I advance!


Jun

 

[vongrunt]

Basically you want to get subtree for given @CustCode and @MaxLevel in a flat result set?

AFAIK all really fast methods require some extra "indexing" column(s) that must be maintained during modification of tree structure - materialized paths, nested sets, nested intervals - without that options are pretty much limited to looping and stack/recursion (=slow).

Check this faq183-5322.

------
heisenbug: A bug that disappears or alters its behavior when one attempts to probe or isolate it
schroedinbug: A bug that doesn't appear until someone reads source code and realizes it never should have worked, at which point the program promptly stops working for everybody until fixed.
[ba

 

[junwebhead]

Thanks vongrunt.

Yeah I think that it is what I want - an inverted tree of n levels deep. I already added index on some fields but has no significant increase in performance.

Thank you very much for the links. I will check those out now. But in the mean time, I want to know if the code above has some flaws that affects the performance.

Jun

 

[vongrunt]

Flaws... AFAIK no. You could separate nonrecursive code (called first) from recursive (also includes spInsertToLevel) but this is more like cosmetical change in this case. Reason for slow performance is recursion/looping - one step per tree node - and DB indexes cannot help there much.

FYI nested sets probably aren't good in this case because of high UPDATE cost.

------
heisenbug: A bug that disappears or alters its behavior when one attempts to probe or isolate it
schroedinbug: A bug that doesn't appear until someone reads source code and realizes it never should have worked, at which point the program promptly stops working for everybody until fixed.
[ba

 

[junwebhead]

Thanks a lot vongrunt! I already started reading the links you gave and its very interesting and I'm learning new things from there. Thank you very much!

Jun