| Author |
 Topic  |
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
 Posted - 07/29/2009 : 17:07:03
|
You mean the code now takes 10 minutes to run?
And 1 hour, 6 minutes and 23 seconds before?
N 56°04'39.26"
E 12°55'05.63" |
 |
|
|
vedjha
Posting Yak Master
India
225 Posts |
Posted - 07/29/2009 : 17:15:04
|
no as you give query it is running since 38 minutes (10 min changes to 38 minutes)
And Before I wrote a Query as we discussed, that takes 1 hour, 6 minutes and 23 seconds.
Ved Prakash Jha |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 07/29/2009 : 17:23:22
|
There must be something else going on.
I just created 100,000 test records, and this code runs in 10 seconds flat!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 d.vaid
FROM CatTree AS c
INNER JOIN #temp AS d ON d.xid = c.vaid
)
SELECT vaid
FROM CatTree
OPTION (MAXRECURSION 0)
N 56°04'39.26"
E 12°55'05.63" |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 07/29/2009 : 17:24:25
|
Do you have circular reference!?
A -> B -> A
or
A -> B -> C -> D -> A
N 56°04'39.26"
E 12°55'05.63" |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 07/29/2009 : 17:27:15
|
Here is how you can avoid circulare referenceIF 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, pth)
AS (
SELECT vaID,
';' + CAST(vaID AS VARCHAR(12)) + ';'
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 d.vaid,
c.pth + CAST(d.vaid AS VARCHAR(MAX)) + ';'
FROM CatTree AS c
INNER JOIN #temp AS d ON d.xid = c.vaid
WHERE c.pth NOT LIKE '%;' + CAST(d.vaid AS VARCHAR(MAX)) + ';%'
)
SELECT vaid
FROM CatTree
OPTION (MAXRECURSION 0)
N 56°04'39.26"
E 12°55'05.63" |
|
|
|
vedjha
Posting Yak Master
India
225 Posts |
Posted - 07/29/2009 : 17:30:29
|
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
Ved Prakash Jha |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 07/29/2009 : 17:47:16
|
I don't think you want help.
If you do, email me the table content and I'll see what is going on.
N 56°04'39.26"
E 12°55'05.63" |
|
|
|
vedjha
Posting Yak Master
India
225 Posts |
Posted - 07/29/2009 : 17:58:45
|
ok, wait....
I have a query which gives me appropriate data but takes more and more time.
as:
WITH CatTree (vaid,dtdoj,cstatus,vspid,vunderspon)
AS
(
SELECT vaID,dtdoj,cstatus,vspid,vunderspon FROM members
WHERE (vspid='M100102' or vunderspon='M100102' ) and (cplacement='R' or vunderplacement='R')
UNION ALL
SELECT C.vaid,C.dtdoj,C.cstatus,C.vspid,C.vunderspon
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'
Note: Bold line makes slow performance , but line is correct.
Ved Prakash Jha |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 07/29/2009 : 18:12:07
|
And now we are back to square 1 again.
N 56°04'39.26"
E 12°55'05.63" |
|
|
|
vedjha
Posting Yak Master
India
225 Posts |
Posted - 07/29/2009 : 18:28:32
|
there is any another option..
Ved Prakash Jha |
|
|
|
vedjha
Posting Yak Master
India
225 Posts |
Posted - 07/30/2009 : 12:48:23
|
Pleae help me to Solve this issue...
Ved Prakash Jha |
|
|
|
vedjha
Posting Yak Master
India
225 Posts |
Posted - 07/30/2009 : 19:12:49
|
Hello Sir,
I am in Problem, please help me.
case study:
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
Details: Sponsor Id Can sponsor infinity no of associates . and associate will get benifited of spill.
spill means Associate has not sponsor Associate But he is Refferal of that associate.
In this structure , we can see that M4 has not sponsor Any Associate but his downline having M6,M7,M8 (Left Side)
if he directly sponsor or join to Associate in System Then it will come his downline.
please help me
Ved Prakash Jha |
|
|
|
vedjha
Posting Yak Master
India
225 Posts |
Posted - 07/30/2009 : 19:13:59
|
Hello Sir,
I am in Problem, please help me.
case study:
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
Details: Sponsor Id Can sponsor infinity no of associates . and associate will get benifited of spill.
spill means Associate has not sponsor Associate But he is Refferal of that associate.
In this structure , we can see that M4 has not sponsor Any Associate but his downline having M6,M7,M8 (Left Side)
if he directly sponsor or join to Associate in System Then it will come his downline.
please help me
Ved Prakash Jha |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 07/31/2009 : 03:51:26
|
I have set up a teslab right now.
I imported your 7688 records and run you original CTE. It takes 11 seconds with no indexes present at all.
Running my suggestion posted 07/29/2009 : 16:02:07, takes less than a second on the 7688 records.
CREATE TABLE #Temp
(
vaid CHAR(7),
xID CHAR(7)
)
INSERT #Temp
(
vaid,
xID
)
SELECT vaid,
vspid as xID
FROM members as m
where dtdoj >= '20090101'
and dtdoj < '20100101'
and vspid is not null
union
SELECT vaid,
vunderspon as xID
FROM members
where dtdoj >= '20090101'
and dtdoj < '20100101'
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'
UNION ALL
SELECT d.vaid
FROM CatTree AS c
INNER JOIN #temp AS d ON d.xid = c.vaid
)
SELECT vaid
FROM CatTree
drop table #temp
N 56°04'39.26"
E 12°55'05.63" |
|
|
|
vedjha
Posting Yak Master
India
225 Posts |
Posted - 07/31/2009 : 05:11:00
|
I have not getting output
it gives me an exception as:
(15376 row(s) affected)
Msg 240, Level 16, State 1, Line 31
Types don't match between the anchor and the recursive part in column "vaID" of recursive query "catTree".
Ved Prakash Jha |
|
|
|
vedjha
Posting Yak Master
India
225 Posts |
Posted - 07/31/2009 : 05:56:05
|
First Time I execute Query as:
CREATE TABLE #Temp
(
vaid CHAR(7),
xID CHAR(7)
)
INSERT #Temp
(
vaid,
xID
)
SELECT vaid,
vspid as xID
FROM members as m
where dtdoj >= '20090101'
and dtdoj < '20100101'
and vspid is not null
union
SELECT vaid,
vunderspon as xID
FROM members
where dtdoj >= '20090101'
and dtdoj < '20100101'
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'
UNION ALL
SELECT d.vaid
FROM CatTree AS c
INNER JOIN #temp AS d ON d.xid = c.vaid
)
SELECT vaid
FROM CatTree
drop table #temp
It gives an Exception as:
(15376 row(s) affected)
Msg 240, Level 16, State 1, Line 31
Types don't match between the anchor and the recursive part in column "vaID" of recursive query "catTree".
Ved Prakash Jha |
|
|
|
vedjha
Posting Yak Master
India
225 Posts |
Posted - 07/31/2009 : 05:58:02
|
After That I Execute Query As:
SELECT vaid,
vspid as xID
FROM members as m
where dtdoj >= '20090101'
and dtdoj < '20100101'
and vspid is not null
union
SELECT vaid,
vunderspon as xID
FROM members
where dtdoj >= '20090101'
and dtdoj < '20100101'
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'
UNION ALL
SELECT d.vaid
FROM CatTree AS c
INNER JOIN #temp AS d ON d.xid = c.vaid
)
SELECT vaid
FROM CatTree
it gives me an exception as:
Msg 240, Level 16, State 1, Line 19
Types don't match between the anchor and the recursive part in column "vaID" of recursive query "catTree".
Ved Prakash Jha |
|
|
|
vedjha
Posting Yak Master
India
225 Posts |
Posted - 07/31/2009 : 08:48:43
|
please help me...
Ved Prakash Jha |
|
|
|
TG
Flowing Fount of Yak Knowledge
USA
5469 Posts |
Posted - 07/31/2009 : 10:38:55
|
Looks like the "patron saint of lost yaks" has finally found a yak so lost they are beyond help...
(I hesitate to interject but I will anyway)
Ved,
I'm not sure what else Peso can do for you. With your exact structure and data he as provided a quick, error free solution. I would now have to question how you are running his suggestions? Did you simply copy/paste his solution into a query window and run it? Or perhaps you re-typed it into a front-end application and are attempting to run it via an interface of some kind?
Or based on the "types don't match" error perhaps your table definition (ie column datatypes) don't match what you provided Peso.
Check the datatype of VAID in [Members] with the type in #temp which is char(7).
Be One with the Optimizer
TG |
|
|
|
vedjha
Posting Yak Master
India
225 Posts |
Posted - 07/31/2009 : 11:58:02
|
Thank u TG, For Guidance.
Ved Prakash Jha |
|
|
|
Topic |
|
Binary Tree
