salim

salim

  • NA
  • 23
  • 0

Sql Query execution

May 3 2010 2:22 AM

 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
 

 
 
 

Answers (2)