I have 2833 rows in my table (PK for UserId Column, No other indexes). From the query plan which use Eager spool, it took 135ms. Sometimes i dun understand why it use another query plan without Eager spool which took 3sec instead. Can someone explain to me why? How can i force it to use Eager spool all the time?
CREATE FUNCTION [dbo].[mChildrenTree]
(
@UserId varchar(12),
@IncludeSelf tinyint = 0
)
RETURNS @ChildrenTable TABLE
(
UserId varchar(12) primary key,
ParentId varchar(12),
[Rank] tinyint,
[Role] varchar(5)
)
AS
Begin
If @IncludeSelf = 1
Begin
Insert Into @ChildrenTable
Select UserId, ParentId, [Rank], [Role]
From mMemberTree Where Userid = @UserId
End
;with show_byUserid (parentid, userid, [rank], [Role])
as
(
select parentid, userid, [rank], [Role] from mMemberTree where ParentId = @UserId
union all
select z.parentid, z.userid, z.[rank], z.[Role] from mMemberTree z inner join show_byUserid t on z.parentid = t.userid
)
Insert Into @ChildrenTable
Select UserId, ParentId, [Rank], [Role]
From show_byUserid
RETURN
End
Eager spool
W/O Eager spool
Inconsistent query plan for recursive
Posted - 06/29/2012 : 01:02:36
