Geek Answers Handbook

CTE Optimization for Recursive Queries

I have a self-join table. You can think of a structure as a standard table for representing an organizational hierarchy. For example, a table: -

MemberId
MemberName
RelatedMemberId

This table consists of 50,000 sample records. I wrote a recursive CTE request and it works absolutely fine. However, the time taken to process just 50,000 records is about 3 minutes on my machine (4 GB RAM, 2.4 GHz Core2Duo, 7200 RPM HDD).

How can I improve performance because 50,000 is not such a huge number. Over time, it will continue to grow. This is a query, which is exactly what I have in my stored procedure. The purpose of the query is to select all members that fall under a particular member. For instance. Every person comes under the owner of the company. For the Manager, except for the Owner, all entries are returned. Hope you understand the purpose of the request.

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 performance, I even did Joins in the last step when selecting records so that all unnecessary records are not inserted into the temp table. If I made a join to the base step and the recursive step CTE (instead of Select in the last step), the request will take 20 minutes!

MemberId is the primary key in the table.

:)

+5
1

Where IsNull(MemberId,0) = IsNull(@MemberId,0) , , NULL =, IS NULL. , .

WHERE MemberId = @MemberId OR (@MemberId IS NULL AND MemberId IS NULL) , .

, RelatedMemberId. ,

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

( , MemberId - , )

+8

More articles:


All Articles