Hi
I am using cte(Common table expretion) to get data . becouse data present in heirerchy form
maen parent-child relation ship .when i use cte to get all child node from top most node ,it take much time (2 minute or more then it ). acchually i get left-right node from table ( there are two table and i use inner join ) and CTE run for every node to get left-right from its child node.
CTE Code is ---
With
member_list
AS
(
Select m.id, m.aa ,m.bb,m.cc,m.dd,m.ee ,ml.qqq,ml.www,ml.yyy from member as m inner join mlm_member as ml on ml.id = m.id and ml.parentid=@sponserID and m.position=@pos
Union All
Select m.m.aa ,m.bb,m.cc,m.dd,m.ee ,ml.id ,ml.qqq,ml.www,ml.yyy From member as m inner join mlm_member as ml on ml.id = m.id Inner Join member_list As mli On ml.parentid = mli.id )
select @no_row=count(*) from member_list Where qqq=@Join and aa='0' and bb='0' and WithOutFee='0' option (maxrecursion 2000)
Many time this cte run in loop as 100 time so it take very time and its very irritation job.
is there any other way to calculate data from table (in heirarchy form)
main probleme is time Taken.
so plese resolve my prob and give me appropriate links