Binary Tree - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Binary Tree

Author

Topic

vedjha
Posting Yak Master

228 Posts

Posted - 2009-07-21 : 13:49:40

Hello Sir,
I have a binary tree in which every Sponsor having 2 associates.
There is a spilling system. It means if u have completed your 2 associate then u can spill ur downline associates.
giving u some data as:

AssociateID Placement Sponsor UnderPlacement Reffered

M2 L M1
M3 R M1
M4 L M2
M5 R M2
M6 M2 L M4
M7 M1 L M6
M8 M1 L M7

i have written a query as:

WITH CatTree (vaid,dtdoj,cstatus)
AS
(

SELECT vaID,dtdoj,cstatus FROM members
WHERE (vspid='M100102' or vunderspon='M100102' ) and (cplacement='L' or vunderplacement='L')

UNION ALL

SELECT C.vaid,C.dtdoj,C.cstatus
FROM members C
INNER JOIN CatTree ON
(
(CatTree.vaid= C.vspid) or (CatTree.vaid= C.vunderspon)
)

)

SELECT distinct vaid FROM CatTree

where dtdoj between '1/1/2009' and '1/1/2010'
and cstatus='V'

=====================

it gives me output corectly.

I have 5300 records in my members table , As I have Process my 2nd no Id then it gives me correct output but it takes 1 hour 6 minutes 23 second. I have to use it online. There is any another process to perform this process in a very minimum time.

please help me , its urgent

Ved Prakash Jha

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-21 : 14:49:57

This may not give the same result, but I think it will run faster

;WITH catTree(vaID)
AS	(
		SELECT	vaID
		FROM	members 
		WHERE	(vspid = 'M100102' or vunderspon = 'M100102')
			and (cplacement = 'L' or vunderplacement = 'L')
			and dtdoj >= '20090101'
			and dtdoj < '20100101'
			and cstatus = 'V'

		UNION ALL

		SELECT		m.vaid
		FROM		members as m 
		INNER JOIN	CatTree AS c ON	c.vaid IN (m.vspid, m.vunderspon)
		where		m.dtdoj >= '20090101'
				and m.dtdoj < '20100101'
				and m.cstatus = 'V'
)

SELECT		vaid
FROM		CatTree
group by	vaid

N 56°04'39.26"
E 12°55'05.63"

vedjha
Posting Yak Master

228 Posts

Posted - 2009-07-21 : 15:22:27

it gives me correct outpt
but it is very slower
I have run this query before some times
it give ne correct output but takes time as 1 hours 6 min 23 sec

please help me....

Ved Prakash Jha

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-21 : 15:28:15

The query takes more than one hour to run, and you post the results after only 30 minutes?
The chance that my suggestion runs in exactly the same time (on the second) as yours, as a thing I don't believe in.

I created a test lab with 5300 records, and my query ran in 2 seconds. Perhaps we have different indexes?

Try this

;WITH c1(vaID)
AS	(
		SELECT	vaID
		FROM	members 
		WHERE	(vspid = 'M100102' or vunderspon = 'M100102')
			and (cplacement = 'L' or vunderplacement = 'L')
			and dtdoj >= '20090101'
			and dtdoj < '20100101'
			and cstatus = 'V'

		UNION ALL

		SELECT		m.vaid
		FROM		members as m 
		INNER JOIN	c1 AS c ON c.vaid = m.vspid
		where		m.dtdoj >= '20090101'
				and m.dtdoj < '20100101'
				and m.cstatus = 'V'
), c2(vaID)
AS	(
		SELECT	vaID
		FROM	members 
		WHERE	(vspid = 'M100102' or vunderspon = 'M100102')
			and (cplacement = 'L' or vunderplacement = 'L')
			and dtdoj >= '20090101'
			and dtdoj < '20100101'
			and cstatus = 'V'

		UNION ALL

		SELECT		m.vaid
		FROM		members as m 
		INNER JOIN	c2 AS c ON c.vaid = m.vunderspon
		where		m.dtdoj >= '20090101'
				and m.dtdoj < '20100101'
				and m.cstatus = 'V'
)

SELECT	vaid
FROM	c1
UNION
SELECT	vaid
FROM	c2

N 56°04'39.26"
E 12°55'05.63"

vedjha
Posting Yak Master

228 Posts

Posted - 2009-07-21 : 19:03:37

I have used this query.
yes it takes 2 sec. but don't give appropriate data.
When it was take 1 hrs 6 min and 23 sec then it gave me 2323 records
but by this query i got only 190 records.

suppose we have data as

AssociateID Placement Sponsor UnderPlacement Reffered

M2 L M1
M3 R M1
M4 L M2
M5 R M2
M6 M2 L M4
M7 M1 L M6
M8 M1 L M7
M9 L M4

in this example M4 has sponsor only one associate but result will come like this:

vaid
~~~~~~~~~~~~~~~

M6
M7
M8
M9

It will return Associate ID as well as M4 spill Associates.

Ved Prakash Jha

vedjha
Posting Yak Master

228 Posts

Posted - 2009-07-21 : 19:20:16

sorry,

I have used this query.
yes it takes 2 sec. but don't give appropriate data.
When it was take 1 hrs 6 min and 23 sec then it gave me 215 records
but by this query i got only 190 records.

suppose we have data as

AssociateID Placement Sponsor UnderPlacement Reffered

M2 L M1
M3 R M1
M4 L M2
M5 R M2
M6 M2 L M4
M7 M1 L M6
M8 M1 L M7
M9 L M4

in this example M4 has sponsor only one associate but result will come like this:

vaid
~~~~~~~~~~~~~~~

M6
M7
M8
M9

It will return Associate ID as well as M4 spill Associates.

Ved Prakash Jha

vedjha
Posting Yak Master

228 Posts

Posted - 2009-07-21 : 22:32:20

I have modify your query as:

WITH c1(vaID)
AS (
SELECT vaID
FROM members
WHERE (vspid = 'M100002' or vunderspon = 'M100002')
and (cplacement = 'L' or vunderplacement = 'L')
and dtdoj between '1/1/2009' and '1/1/2010'
and cstatus = 'V'

UNION ALL

SELECT m.vaid
FROM members as m
INNER JOIN c1 AS c ON (c.vaid = m.vunderspon or c.vaid = m.vspid)
where m.dtdoj between '1/1/2009' and '1/1/2010'
and m.cstatus = 'V'
), c2(vaID)
AS (
SELECT vaID
FROM members
WHERE (vspid = 'M100002' or vunderspon = 'M100002')
and (cplacement = 'L' or vunderplacement = 'L')
and dtdoj between '1/1/2009' and '1/1/2010'
and cstatus = 'V'

UNION ALL

SELECT m.vaid
FROM members as m
INNER JOIN c2 AS c ON c.vaid = m.vunderspon
where m.dtdoj between '1/1/2009' and '1/1/2010'
and m.cstatus = 'V'
)

SELECT vaID
FROM c1
UNION
SELECT vaID
FROM c2

=========

I have add only add, condition in join as
SELECT m.vaid
FROM members as m
INNER JOIN c1 AS c ON (c.vaid = m.vunderspon or c.vaid = m.vspid)

it gives me correct data but it takes 1 hrs 6 min 23 sec.

it takes time a lot

Ved Prakash Jha

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-22 : 02:34:35

Try this to see if it runs faster than 1 hour 6 minutes and 23 seconds

;WITH catTree(vaID)
AS	(
		SELECT	vaID
		FROM	members 
		WHERE	(vspid = 'M100102' or vunderspon = 'M100102')
			and (cplacement = 'L' or vunderplacement = 'L')
			and dtdoj >= '20090101'
			and dtdoj < '20100101'
			and cstatus = 'V'

		UNION ALL

		SELECT		x.vaid
		FROM		CatTree AS c
		INNER JOIN	(
					SELECT	vaid,
						vspid as xID
					FROM	members as m 
					where	dtdoj >= '20090101'
						and dtdoj < '20100101'
						and cstatus = 'V'
						and vspid is not null

					union

					SELECT	vaid,
						vunderspon as xID
					FROM	members  
					where	dtdoj >= '20090101'
						and dtdoj < '20100101'
						and cstatus = 'V'
						and vunderspon is not null
				) AS d ON d.xid = c.vaid
)

SELECT		vaid
FROM		CatTree
group by	vaid

N 56°04'39.26"
E 12°55'05.63"

vedjha
Posting Yak Master

228 Posts

Posted - 2009-07-23 : 14:38:25

As I used This Query, It gives me message as

Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "x.vaid" could not be bound.
Msg 466, Level 16, State 1, Line 1
UNION operator is not allowed in the recursive part of a recursive common table expression 'catTree'.

Ved Prakash Jha

vedjha
Posting Yak Master

228 Posts

Posted - 2009-07-23 : 14:45:10

As I modified , slightly hange in query as

;WITH catTree(vaID)
AS (
SELECT vaID
FROM members
WHERE (vspid = 'M100102' or vunderspon = 'M100102')
and (cplacement = 'L' or vunderplacement = 'L')
and dtdoj >= '20090101'
and dtdoj < '20100101'
and cstatus = 'V'

UNION ALL

SELECT c.vaid
FROM CatTree AS c
INNER JOIN (
SELECT vaid,
vspid as xID
FROM members as m
where dtdoj >= '20090101'
and dtdoj < '20100101'
and cstatus = 'V'
and vspid is not null

union

SELECT vaid,
vunderspon as xID
FROM members
where dtdoj >= '20090101'
and dtdoj < '20100101'
and cstatus = 'V'
and vunderspon is not null
) AS d ON d.xid = c.vaid
)

SELECT vaid
FROM CatTree
group by vaid

it gives me an exception as:

Msg 466, Level 16, State 1, Line 1
UNION operator is not allowed in the recursive part of a recursive common table expression 'catTree'.

please help me...

Ved Prakash Jha

vedjha
Posting Yak Master

228 Posts

Posted - 2009-07-23 : 16:24:45

please help me

Ved Prakash Jha

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-23 : 16:43:28

[code]CREATE TABLE #Temp
(
vaid INT,
xID INT
)

INSERT #Temp
(
vaid,
xID
)
SELECT vaid,
vspid as xID
FROM members as m
where dtdoj >= '20090101'
and dtdoj < '20100101'
and cstatus = 'V'
and vspid is not null

union

SELECT vaid,
vunderspon as xID
FROM members
where dtdoj >= '20090101'
and dtdoj < '20100101'
and cstatus = 'V'
and vunderspon is not null

create clustered index ix_temp ON #temp (xid, vaid)

;WITH catTree(vaID)
AS (
SELECT vaID
FROM members
WHERE (vspid = 'M100102' or vunderspon = 'M100102')
and (cplacement = 'L' or vunderplacement = 'L')
and dtdoj >= '20090101'
and dtdoj < '20100101'
and cstatus = 'V'

UNION ALL

SELECT x.vaid
FROM CatTree AS c
INNER JOIN #temp AS d ON d.xid = c.vaid
)

SELECT vaid
FROM CatTree[/code]

N 56°04'39.26"
E 12°55'05.63"

vedjha
Posting Yak Master

228 Posts

Posted - 2009-07-23 : 17:09:22

firstly iexecute this query

CREATE TABLE #Temp
(
vaid varchar(20),
xID varchar(20)
)

after that

INSERT #Temp
(
vaid,
xID
)
SELECT vaid,
vspid as xID
FROM members as m
where dtdoj >= '20090101'
and dtdoj < '20100101'
and cstatus = 'V'
and vspid is not null

union

SELECT vaid,
vunderspon as xID
FROM members
where dtdoj >= '20090101'
and dtdoj < '20100101'
and cstatus = 'V'
and vunderspon is not null

after that

create clustered index ix_temp ON #temp (vaid, xid)

after that

;WITH catTree(vaID)
AS (
SELECT vaID
FROM members
WHERE (vspid = 'M100102' or vunderspon = 'M100102')
and (cplacement = 'L' or vunderplacement = 'L')
and dtdoj >= '20090101'
and dtdoj < '20100101'
and cstatus = 'V'

UNION ALL

SELECT x.vaid
FROM CatTree AS c
INNER JOIN #temp AS d ON d.xid = c.vaid
)

then it gives an exception as

Msg 102, Level 15, State 1, Line 17
Incorrect syntax near ')'.

i am not getting understand why this error comes

please help

Ved Prakash Jha

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-23 : 17:28:56

You need a SELECT attached to the CTE statement.
See my edited reply above.

N 56°04'39.26"
E 12°55'05.63"

vedjha
Posting Yak Master

228 Posts

Posted - 2009-07-29 : 15:18:54

Now till date I am facing This problem
please help me...

Ved Prakash Jha

vedjha
Posting Yak Master

228 Posts

Posted - 2009-07-29 : 15:36:53

WITH CatTree (vaid,dtdoj,cstatus)
AS
(

SELECT vaID,dtdoj,cstatus FROM members
WHERE (vspid='M100002' or vunderspon='M100002' ) and (cplacement='R' or vunderplacement='R')

UNION ALL

SELECT C.vaid,C.dtdoj,C.cstatus
FROM members C
INNER JOIN CatTree ON
(
(CatTree.vaid= C.vspid) or (CatTree.vaid= C.vunderspon)
)

)

SELECT * FROM CatTree
where dtdoj between '1/1/2009' and '1/1/2010'
and cstatus='V'

==================

or (CatTree.vaid= C.vunderspon)

It process late whien Level is 100-500 depth. it gives us spill data.
it takes more than 1 hour to process

when i remove this line then process complete in 3-4 sec
It give only Downline Data.

But I have to use this line as query. I need both, Spill as well as downline data.
but it takes more time . so please help me to process it fast...

Ved Prakash Jha

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-29 : 16:02:07

N 56°04'39.26"
E 12°55'05.63"

vedjha
Posting Yak Master

228 Posts

Posted - 2009-07-29 : 16:10:16

When I execute this , It throws an Exception as

(15375 row(s) affected)
Msg 1913, Level 16, State 1, Line 19
The operation failed because an index or statistics with name 'ix_temp' already exists on table '#temp'.

When I run it from Select query as:

SELECT vaid,
vspid as xID
FROM members as m
where dtdoj >= '20090101'
and dtdoj < '20100101'
and cstatus = 'V'
and vspid is not null

union

SELECT vaid,
vunderspon as xID
FROM members
where dtdoj >= '20090101'
and dtdoj < '20100101'
and cstatus = 'V'
and vunderspon is not null

--create clustered index ix_temp ON #temp (xid, vaid)

;WITH catTree(vaID)
AS (
SELECT vaID
FROM members
WHERE (vspid = 'M100102' or vunderspon = 'M100102')
and (cplacement = 'L' or vunderplacement = 'L')
and dtdoj >= '20090101'
and dtdoj < '20100101'
and cstatus = 'V'

UNION ALL

SELECT c.vaid
FROM CatTree AS c
INNER JOIN #temp AS d ON d.xid = c.vaid
)

SELECT vaid
FROM CatTree
order by vaid

it gives me an exception as :

(15375 row(s) affected)
Msg 530, Level 16, State 1, Line 21
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

Ved Prakash Jha

vedjha
Posting Yak Master

228 Posts

Posted - 2009-07-29 : 16:13:57

I have to run it online. now there is approx 8000 user visits this data daily. so process must be fast and scalable.
please help me. ad number of User will Increment. As I am calculating Bussiness after 3 or 4 months site having more than 50-60 thousand user. so please help me to perform fast process...

Ved Prakash Jha

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-29 : 16:15:17

[code]IF OBJECT_ID('TempDB..#Temp') IS NOT NULL
DROP TABLE #Temp

CREATE TABLE #Temp
(
vaid INT,
xID INT
)

INSERT #Temp
(
vaid,
xID
)
SELECT vaid,
vspid as xID
FROM members as m
where dtdoj >= '20090101'
and dtdoj < '20100101'
and cstatus = 'V'
and vspid is not null

union

SELECT vaid,
vunderspon as xID
FROM members
where dtdoj >= '20090101'
and dtdoj < '20100101'
and cstatus = 'V'
and vunderspon is not null

create clustered index ix_temp ON #temp (xid, vaid)

;WITH catTree(vaID)
AS (
SELECT vaID
FROM members
WHERE (vspid = 'M100102' or vunderspon = 'M100102')
and (cplacement = 'L' or vunderplacement = 'L')
and dtdoj >= '20090101'
and dtdoj < '20100101'
and cstatus = 'V'

UNION ALL

SELECT x.vaid
FROM CatTree AS c
INNER JOIN #temp AS d ON d.xid = c.vaid
)

SELECT vaid
FROM CatTree
OPTION (MAXRECURSION 0)[/code]

N 56°04'39.26"
E 12°55'05.63"

vedjha
Posting Yak Master

228 Posts

Posted - 2009-07-29 : 16:46:13

I have ran it before 10 min utes and it is in process

Ved Prakash Jha

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources