Why does this function perform better than query? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

Why does this function perform better than query?

Author

Topic

ferrethouse
Constraint Violating Yak Guru

352 Posts

Posted - 2011-09-01 : 11:45:47

Why does this function perform better than a straight query to a single table?

So I created a table that stores all of the courses contained in a learning path. It is called LearningPathCourseFlat. I run this query...

select courseid,lessonid from LearningPathCourseFlat where learningpathid = 2

The table is properly indexed and the plan shows that it is using the index (index seek). So I've setup everything correctly from what I can tell.

So why is this call 3 times faster...

select * from FuncLearningPathCourses(2)

In the function it does several joins and writes to a temporary table. It should be slower!!! But it is faster. Here is the code for the function...


USE [absorb]
GO
/****** Object:  UserDefinedFunction [dbo].[FuncLearningPathCourses]    Script Date: 09/01/2011 09:45:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[FuncLearningPathCourses] (@LearningPathId INT)
RETURNS @CourseIds TABLE
	(
	CourseID INT,
	LessonID INT NULL
	)

AS
BEGIN
	IF (@LearningPathId > 0)
	BEGIN
		INSERT INTO @CourseIds SELECT LearningPathCourses.CourseID, LessonID FROM LearningPathCourseGroups INNER JOIN LearningPathCourseGroupCourses ON LearningPathCourseGroupCourses.LearningPathCourseGroupID = LearningPathCourseGroups.LearningPathCourseGroupID INNER JOIN LearningPathCourses ON LearningPathCourses.LearningPathCourseID = LearningPathCourseGroupCourses.LearningPathCourseID WHERE LearningPathCourseGroups.LearningPathID = @LearningPathId
	END
	RETURN
END

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2011-09-01 : 13:51:10

The Plan in the cache....Can you try running by cleaning cache for both? Do it on DEV Environment??

ferrethouse
Constraint Violating Yak Guru

352 Posts

Posted - 2011-09-01 : 15:20:28

Hmmm. Cleared the plan cache on dev using...

DBCC FREEPROCCACHE

They now are 50% cost each. Less odd - but still odd.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources