Inconsistent query plan for recursive

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Inconsistent query plan for recursive

Author

Topic

Samuelg78
Starting Member

4 Posts

Posted - 2012-06-29 : 01:02:36

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-29 : 22:54:00

what were values for @IncludeSelf parameter in both case?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources