Optimising CTE for recursive queries

Question

I have a table with self join. You can think of the structure as standard table to represent organisational hierarchy. Eg table:-

MemberId
MemberName
RelatedMemberId

This table consists of 50000 sample records. I wrote CTE recursive query and it works absolutely fine. However the time it takes to process just 50000 records is round about 3 minutes on my machine (4GB Ram, 2.4 Ghz Core2Duo, 7200 RPM HDD).

How can I possibly improve the performance because 50000 is not so huge number. Over time it will keep on increasing. This is the query which is exactly what I have in my Stored Procedure. The query's purpose is to select all the members that come under a specific member. Eg. Under Owner of the company each and every person comes. For Manager, except Owner all of the records gets returned. I hope you understand the query's purpose.

SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO

Alter PROCEDURE spGetNonVirtualizedData
(
    @MemberId    int
)
AS
BEGIN

    With MembersCTE As
    (
        Select parent.MemberId As MemberId, 0 as Level
            From Members as parent Where IsNull(MemberId,0) = IsNull(@MemberId,0)

                    Union ALL
        Select    child.MemberId As MemberId , Level + 1 as Level
            From Members  as child
                Inner Join MembersCTE on MembersCTE.MemberId = child.RelatedMemberId
    )   
    Select Members.*
        From MembersCTE
        Inner Join Members On MembersCTE.MemberId = Members.MemberId
        option(maxrecursion 0)

END
GO

As you can see to improve the performance, I have even made the Joins at the last step while selecting records so that all unnecessary records do not get inserted into temp table. If I made joins in my base step and recursive step of CTE (instead of Select at the last step) the query takes 20 minutes to execute!

MemberId is primary key in the table.

Thanks in advance :)

Answer 1

In your anchor condition you have Where IsNull(MemberId,0) = IsNull(@MemberId,0) I assume this is just because when you pass NULL as a parameter = doesn't work in terms of bringing back IS NULL values. This will cause a scan rather than a seek.

Use WHERE MemberId = @MemberId OR (@MemberId IS NULL AND MemberId IS NULL) instead which is sargable.

Also I'm assuming that you can't have an index on RelatedMemberId. If not you should add one

CREATE NONCLUSTERED INDEX ix_name ON Members(RelatedMemberId) INCLUDE (MemberId)

(though you can skip the included column bit if MemberId is the clustered index key as it will be included automatically)