TECHNOLOGIES
FORUMS
JOBS
BOOKS
EVENTS
INTERVIEWS
Live
MORE
LEARN
Training
CAREER
MEMBERS
VIDEOS
NEWS
BLOGS
Sign Up
Login
No unread comment.
View All Comments
No unread message.
View All Messages
No unread notification.
View All Notifications
Answers
Post
An Article
A Blog
A News
A Video
An EBook
An Interview Question
Ask Question
Forums
Monthly Leaders
Forum guidelines
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
Reply
Answers (
2
)
When to use HTML controls over ASP.NET controls?
an error has occurred while establishing a connection to the server