October 1, 2008 at 4:17 am
Hi
I need to know how gud it is to use subqueries in a sql query in place of field selection.
I have a table which stores all child nodes of a member (of a tree).
I have to get report on child members under my team with count of nodes under their respective left,middle and right nodes
e.g. For member A, it should be like,
Member Left Middle Right
B 10 5 5
C 5 8 10
.
.
.
.
.so on
Table structure is like:
Nr_id Nr_parent Nr_child Nr_pos (L/M/R)
Query am trying to use is like:
SELECT nr_child AS child, nr_pos,
(SELECT COUNT(*)
FROM db_noderelation
WHERE nr_pos = 'L' AND nr_parent = nr.nr_child) AS LNodes,
(SELECT COUNT(*)
FROM db_noderelation
WHERE nr_pos = 'M' AND nr_parent = nr.nr_child) AS MNodes,
(SELECT COUNT(*)
FROM db_noderelation
WHERE nr_pos = 'R' AND nr_parent = nr.nr_child) AS RNodes
FROM db_noderelation nr
WHERE (nr_parent = 'A')
There will be lot records in the table. Is this a gud method according to performance wise?
If any better suggestion pls let me know.
October 1, 2008 at 6:48 am
Look at the execution plan. It is going to generate loops for the correlated sub-queries you have. MS SQL is not good at looping and this type of hidden RBAR is not good. You could handle the same counting with a case statement and are very likely to end up with better performance.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply