table-join selection performance: get members from nested groups (large amount)

Question

I have three tables which define member & group profiles like so:

[tbMember] (1M records): MemberID(PK) | MemberName | Age | Sex

[tbGroup] (10K records): GroupID(PK) | GroupName | ParentGroupID

[tbMemberGrouopRelation] (2M records): MemberID | GroupID

Groups are ordered in hierarchical structure:

     /--- 2
1 --/---- 3    /--- 5
    \---- 4 --/---- 6
              \---- 7

When I grab some members from particular group (and all its subgroups), I write my SQL like (in SQL Server 2012):

SELECT m.MemberID, m.MemberName, m.Age, m.Sex
FROM tbMember m
  LEFT JOIN tbMemberGroupRelation mg ON (mg.MemberID = m.MemberID)
WHERE mg.GroupID IN (
    SELECT GroupID FROM FN_GetAllSubgroups(1)
  )
ORDER BY m.MemberID DESC
OFFSET 1000 ROWS FETCH NEXT 40 ROWS ONLY

Where FN_GetAllSubgroups implemented like this:

CREATE FUNCTION [dbo].[GetAllSubgroups] (@parentID int)
RETURNS @t TABLE(GroupID int, ParentID int, GroupName nvarchar(128))
BEGIN
    WITH GroupList 
    AS
    (
        SELECT GroupID, ParentID, GroupName
        FROM dbo.tbGroup
        WHERE GroupID = @parentID UNION ALL
        SELECT a.GroupID, a.ParentID, a.GroupName 
        FROM tbGroup a
            INNER JOIN GroupList b on a.ParentID = b.GroupID
    )
    INSERT INTO @t
    SELECT a.GroupID, a.ParentID, a.GroupName FROM dbo.tbGroup a
        INNER JOIN GroupList b on a.GroupID = b.GroupID
    ORDER BY a.ParentID, a.DisplayOrder
    RETURN
END

GO

-- index on tbGroup is ParentID (INCLUDE[GroupID, GroupName, DisplayOrder])

The function itself processes quite fast, even selecting from a top node group (returns 10k subgroups), processing time is within 200ms

However, when I do table join selection that select members from particular subgroup collections, it becomes extremely slow (look at my first SQL). I checked execution plan and it tells me Clurster Index Seek on tbMember takes up to 97% of time, Estimate rows = 1 and actual rows usually >= 20K

Are there any solutions to make it faster ?

Highly appreciate your help !

======================================

UPDATE: Execution result pasted below

(40 row(s) affected)

Table '_tbMember'. Scan count 0, logical reads 4679267, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table '_tbMemberGroupRelation'. Scan count 9516, logical reads 32484, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table '#AC4BBD06'. Scan count 1, logical reads 51, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

SQL Server Execution Times:

CPU time = 2953 ms, elapsed time = 9143 ms.

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 0 ms.

Rewrite your function into an iTVF and see if there's improvement. — Felix Pamittan
Can you post your actual execution plan and schema of those tables involved — TheGameiswar
stackoverflow.com/questions/7359702/… ..you can use pastebin,if this is long — TheGameiswar
CREATE FUNCTION [dbo].[GetAllSubgroups] (@parentID int) RETURNS TABLE AS RETURN WITH GroupList AS( SELECT GroupID, ParentID, GroupName FROM dbo.tbGroup WHERE GroupID = @parentID UNION ALL SELECT a.GroupID, a.ParentID, a.GroupName FROM tbGroup a INNER JOIN GroupList b on a.ParentID = b.GroupID ) SELECT a.GroupID, a.ParentID, a.GroupName FROM dbo.tbGroup a INNER JOIN GroupList b on a.GroupID = b.GroupID — Felix Pamittan
TY! It works! almost 5 times faster than original query. when selecting members from top group node, it returns in about 2s. (compare to > 10s my version) @FelixPamittan — ineztia

Felix Pamittan Felix Pamittan · Accepted Answer · 2016-06-02T01:25:40

You could try transforming your function into an iTVF:

CREATE FUNCTION [dbo].[GetAllSubgroups] (
    @parentID int
)
RETURNS TABLE
AS
RETURN 

WITH GroupList AS (
    SELECT
        GroupID,
        ParentID,
        GroupName
    FROM dbo.tbGroup
    WHERE GroupID = @parentID
    UNION ALL
    SELECT
        a.GroupID,
        a.ParentID,
        a.GroupName
    FROM tbGroup a
    INNER JOIN GroupList b
        ON a.ParentID = b.GroupID
)
SELECT
    a.GroupID,
    a.ParentID,
    a.GroupName
FROM dbo.tbGroup a
INNER JOIN GroupList b
    ON a.GroupID = b.GroupID

table-join selection performance: get members from nested groups (large amount)

1 Answers