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 5604'39.26"
E 1255'05.63"
Go to Top of Page

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
Go to Top of Page

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 5604'39.26"
E 1255'05.63"
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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 5604'39.26"
E 1255'05.63"
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

vedjha
Posting Yak Master

228 Posts

Posted - 2009-07-23 : 16:24:45
please help me


Ved Prakash Jha
Go to Top of Page

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 5604'39.26"
E 1255'05.63"
Go to Top of Page

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
Go to Top of Page

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 5604'39.26"
E 1255'05.63"
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-29 : 16:02:07
[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 5604'39.26"
E 1255'05.63"
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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 5604'39.26"
E 1255'05.63"
Go to Top of Page

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
Go to Top of Page
    Next Page

- Advertisement -