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.
| Author |
Topic |
|
vedjha
Posting Yak Master
228 Posts |
 Posted - 2009-11-27 : 22:10:38
|
Hello sir,I have a procedure, which takes approx 1 hrs to execute. I have to run this procedure on web. so it must take less to less time on webprocedure:~~~~~~~~~~~~~WITH CatTree (vaid, vspID,cplacement,vunderspon,vunderplacement,Level) AS ( SELECT vaID, vspID,cplacement,vunderspon,vunderplacement, 1 As [Level] FROM members WHERE vspid='M116913' and cplacement='R' UNION ALL SELECT C.vaid, C.vspID,C.cplacement,C.vunderspon,C.vunderplacement, Level + 1 FROM members C INNER JOIN CatTree ON ((CatTree.vaid= C.vspid) or (CatTree.vaid= C.vunderspon)) ) SELECT * FROM CatTree=================================================I have approx 1.5 Lakh Data. starting ID takes more time.Help me to make it more fast Ved |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-11-27 : 22:12:34
|
| what are indexes you've on current table? |
 |
|
|
vedjha
Posting Yak Master
228 Posts |
Posted - 2009-11-27 : 22:40:41
|
| vaid columnVed |
|
|
|
vedjha
Posting Yak Master
228 Posts |
Posted - 2009-11-27 : 22:41:46
|
| I am giving u mu case study~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~spill-> Reffered IDHello Sir ,I have to find Downline details of User such as UserID Placement Sponsor UnderPlacement RefferedM2 L M1 M3 R M1 M4 L M2 M5 R M2 M6 M2 L M4M7 M1 L M6M8 M1 L M7it means M1->M2M1->M3M2->M4M2->M5M2->M6M1->M7M1->M8Graphical Image as: M1 M2 M3 M4 M5 M6 M7 M8Note: Every user can make(sponsor) infinity no of user . Bt every user having their two hands Left and Right. If user make two user it means his both hand is full. if he wants make(sponsor) more than two user then he will make user under his downline. it will be said as spilling. Users can sponsor or make infinty no of user under his downline.some question to clearify this example as,Q) To find Users, Sponsored by M1Ans.M1~~~~M2M3M4M5M6M7M8Q) To find Users, Sponsored by M2Ans.M2~~~~M4M5M6M7M8Q) To find Users, Sponsored by M3Ans. NO USER AVILABLEQ) To find Users, Sponsored by M4 Ans. SINCE M4 HAS NOT SPONSORED ANY USER, BUT USERS ARE SPONSORED BY M4'S SPONSOR(UPPER USER OF M4) AND COME(SPILL) UNDER M4. SO M4 HAVING 3 USERS AS,M4~~~~M6M7M8Q) To find Users, Sponsored by M5Ans. NO USER AVILABLEQ) To find Users, Sponsored by M6 Ans. SINCE M6 HAS NOT SPONSORED ANY USER, BUT USERS ARE SPONSORED BY M6'S SPONSOR(UPPER USER OF M6) AND COME(SPILL) UNDER M6. SO M4 HAVING 2 USERS AS,M6~~~~M7M8Q) To find Users, Sponsored by M7 Ans. SINCE M7 HAS NOT SPONSORED ANY USER, BUT USERS ARE SPONSORED BY M7'S SPONSOR(UPPER USER OF M7) AND COME(SPILL) UNDER M7. SO M7 HAVING 1 USERS AS,M7~~~~M8Q) To find Users, Sponsored by M8Ans. NO USER AVILABLE===================================now my question is, How to find users which is made by M2Ved |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-11-28 : 01:39:00
|
| sorry i didnt understand how you get M7M8 in result for users sponsored by M2? |
|
|
|
|
|
|
|
|
Help me to make procedure more Fast