USE [absorb]
GO
/****** Object:  UserDefinedFunction [dbo].[FuncDepartmentChildren]    Script Date: 10/25/2011 13:21:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[FuncDepartmentChildren] (@DeptID int, @Inclusive int)
RETURNS @DepartmentIDs TABLE
	(
	DepartmentID int
	)

AS
BEGIN
	IF (@DeptID > 0)
	BEGIN		
		-- Insert the base department if flagged
		IF @Inclusive = 1
			BEGIN
				INSERT INTO @DepartmentIDs VALUES (@DeptID)
			END
		
		INSERT INTO @DepartmentIds SELECT ChildDepartmentID FROM DepartmentChildren WHERE DepartmentID = @DeptID
	END
	RETURN
END

ALTER FUNCTION [dbo].[FuncDepartmentChildren] (@DeptID int, @Inclusive int)
RETURNS TABLE AS
RETURN(	SELECT ChildDepartmentID FROM DepartmentChildren 
	WHERE @DeptID>0 AND DepartmentID = @DeptID
	UNION ALL
	SELECT DeptID FROM (SELECT @DeptID DeptID) a
	WHERE DeptID>0 AND @Inclusive=1)

quote:

---

Originally posted by Transact Charlie

http://stackoverflow.com/questions/2554333/multi-statement-table-valued-function-vs-inline-table-valued-function

An inline implementation should be treated as if it were a view so I'd expect it not to write to tempdb unless the parent query needs to use a worktable.



Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

---

quote:

---

Originally posted by ferrethouse

Currently my application calls a function called "FuncDepartmentChildren" about 5 million times per hour. I have been investigating tempdb performance and found that this query causes the largest stress on tempdb of all queries. The function itself is dead simple so I'm thinking it would be better to use a stored proc or just call the SELECT statement directly inline so that writes to tempdb wouldn't be needed. Here is the function. Advice appreciated...

USE [absorb]
GO
/****** Object:  UserDefinedFunction [dbo].[FuncDepartmentChildren]    Script Date: 10/25/2011 13:21:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[FuncDepartmentChildren] (@DeptID int, @Inclusive int)
RETURNS @DepartmentIDs TABLE
	(
	DepartmentID int
	)

AS
BEGIN
	IF (@DeptID > 0)
	BEGIN		
		-- Insert the base department if flagged
		IF @Inclusive = 1
			BEGIN
				INSERT INTO @DepartmentIDs VALUES (@DeptID)
			END
		
		INSERT INTO @DepartmentIds SELECT ChildDepartmentID FROM DepartmentChildren WHERE DepartmentID = @DeptID
	END
	RETURN
END

---

quote:

---

Originally posted by Sachin.Nand

<snip>but a plan reuse for an inline function will only occur when the main statement calling the inline function is executed with the same set of parameters<snip>

---

quote:

---

So the answer is not as simple as either its black or its white and that always the MVF are the bad guys.The answer is..it depends..

---

quote:

---

Originally posted by Lamprey

quote:

---

Originally posted by Sachin.Nand

<snip>but a plan reuse for an inline function will only occur when the main statement calling the inline function is executed with the same set of parameters<snip>

---

Do you have any links to show that is true?

---

Use AdventureWorks
go

create function fn_inline(@ProductId int)
returns table
as
return
select WorkOrderID,OrderQty from Production.WorkOrder where ProductID=@ProductId

go
create function fn_multivalued(@ProductId int)
returns @t table(WorkOrderID int,OrderQty int)
as begin

insert @t
select WorkOrderID,OrderQty from Production.WorkOrder where ProductID=@ProductId

return
end
go

dbcc freeproccache
go

Use AdventureWorks
go
select * from fn_inline(722)

select * from fn_multivalued(722)

Use AdventureWorks
go
select usecounts,text,cacheobjtype,objtype from sys.dm_exec_cached_plans 
cross apply  sys.dm_exec_sql_text(plan_handle)
where text like '%function fn_%'

quote:

---

Originally posted by Lamprey
Agreed. I don't see how anyone can possibly answer the OPs origianl question as we don't know in what context the funtion is being used.

---

quote:

---

Originally posted by Sachin.Nand

quote:

---

Originally posted by Lamprey
Agreed. I don't see how anyone can possibly answer the OPs origianl question as we don't know in what context the funtion is being used.

---

Well that's the reason I did suggest the OP to overall get rid of the function itself as he was facing performance issue with it and instead use a temp table.

PBUH

---

quote:

---

Originally posted by Lamprey

I don't get the same resutls as you. I see the inline function being reused almost every time. When I say almost I mean 20 out of 25 times. I tried a combination of literal values and variables (assigning a variable and passing that). So, I don't think you statement that "plan reuse for an inline function will only occur when the main statement calling the inline function is executed with the same set of parameters" is 100% true.

---

quote:

---

Originally posted by Lamprey

quote:

---

Originally posted by Sachin.Nand

quote:

---

Originally posted by Lamprey
Agreed. I don't see how anyone can possibly answer the OPs origianl question as we don't know in what context the funtion is being used.

---

Well that's the reason I did suggest the OP to overall get rid of the function itself as he was facing performance issue with it and instead use a temp table.

PBUH

---

How does a temp table solve the issue? BTW, That's a rhetorical question. We don't know how the function is being used, thus we can't say it will solve the issue. It's possible that the function is being used as part of an OUTER APPLY or something on a large table. We don't know. A temp table may well be the solution. But, until the OP gives more context it's impossible to offer anything but a shot in the dark.

---

quote:

---

Originally posted by Sachin.Nand

quote:

---

Originally posted by Lamprey

I don't get the same resutls as you. I see the inline function being reused almost every time. When I say almost I mean 20 out of 25 times. I tried a combination of literal values and variables (assigning a variable and passing that). So, I don't think you statement that "plan reuse for an inline function will only occur when the main statement calling the inline function is executed with the same set of parameters" is 100% true.

---

Whats the cacheobjtype you are getting for the inline function ?

PBUH

---

quote:

---

Originally posted by Sachin.Nand

Oh please a function with an OUTER APPLY...

A Inline function is very much part of the main query just like views but MVF is always evaluated in a separate context from the main query and the results of MVF are evaluated in temp work tables..Index spools..

The whole separate context and worktables make MVF very costly.If you combine a MVF with a APPLY clause sql server will execute the MVF and populate the worktable multiple times once for each row generated by the apply clause.

Makes sense ??


PBUH

---

quote:

---

Originally posted by Lamprey

quote:

---

Originally posted by Sachin.Nand

quote:

---

Originally posted by Lamprey

I don't get the same resutls as you. I see the inline function being reused almost every time. When I say almost I mean 20 out of 25 times. I tried a combination of literal values and variables (assigning a variable and passing that). So, I don't think you statement that "plan reuse for an inline function will only occur when the main statement calling the inline function is executed with the same set of parameters" is 100% true.

---

Whats the cacheobjtype you are getting for the inline function ?

PBUH

---

I'm getting a Parse Tree.

---

quote:

---

Originally posted by Lamprey

quote:

---

Originally posted by Sachin.Nand

Oh please a function with an OUTER APPLY...

A Inline function is very much part of the main query just like views but MVF is always evaluated in a separate context from the main query and the results of MVF are evaluated in temp work tables..Index spools..

The whole separate context and worktables make MVF very costly.If you combine a MVF with a APPLY clause sql server will execute the MVF and populate the worktable multiple times once for each row generated by the apply clause.

Makes sense ??


PBUH

---

Are you saying people don't use functions in outer applys or do you think its a silly thing to do? I think it's silly, but I wouldn't dismiss people from doing anything.

I get how functions work. My only point is that speculating on a solution is pointless since we don't know how the function is used. Nothing more. Nothing less.

---

var departments = from d in db.FuncDepartmentChildren(NetGrid.DepartmentID, 1) select d.DepartmentID;

        var report = from t in db.Tasks
                     where t.ClientID == NetGrid.ClientIDLMS
                     && (NetGrid.DepartmentID == 0 || NetGrid.DepartmentSelect != 0 || t.DepartmentID == NetGrid.DepartmentID)
                     && (NetGrid.DepartmentID == 0 || NetGrid.DepartmentSelect != 2 || t.Department.ParentDeptID == NetGrid.DepartmentID)
                     && (NetGrid.DepartmentID == 0 || NetGrid.DepartmentSelect != 1 || departments.Contains(t.DepartmentID))
                     select new
                     {
                         ID = t.TaskID,
                         Category = t.TaskCategory,
                         Task = t.TaskName,
                         Description = t.TaskDescription,
                         Department = t.Department.DepartmentName,
                         Steps = t.TaskSteps.Count(),
                         RiskLevel = db.DescTypes.Where(d => d.TypeGroup == TypeGroups.Hazard
                             && Convert.ToInt32(d.Type) <= (t.TaskRatingProbability * t.TaskRatingExposure * t.TaskRatingSeverity)
                             && Convert.ToInt32(d.TypeRange) >= (t.TaskRatingProbability * t.TaskRatingExposure * t.TaskRatingSeverity)).FirstOrDefault().TypeDesc,
                         RiskRating = (t.TaskRatingProbability * t.TaskRatingExposure * t.TaskRatingSeverity),
                         Added = t.TaskAdded,
                         Reviewed = t.TaskEdited,
                     };

        return report;

var childdepartments= from t in db.DepartmentChildren where t.inclusive=1;select t.DepartmentID;
var departments = from d in db.Departments; select d.DepartmentID;
var DepartmentChildren=from dept in  departments from childdpt in childdepartments where dept.DepartmentID==childdpt.DepartmentID

quote:

---

Originally posted by Sachin.Nand

GREAT...Now we are back to square one.Why do you need a function in the first place.It could had easily been achieved by this

var childdepartments= from t in db.DepartmentChildren where t.inclusive=1;select t.DepartmentID;
var departments = from d in db.Departments; select d.DepartmentID;
var DepartmentChildren=from dept in  departments from childdpt in childdepartments where dept.DepartmentID==childdpt.DepartmentID

PBUH

---