Table-valued function optimization

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 2005 Forums

Transact-SQL (2005)

Table-valued function optimization

Author

Topic

aagren
Starting Member

8 Posts

Posted - 2009-09-28 : 05:37:23

Hi,
Have a problem with function performance.

The original (outer) function has an inline function call to an (inner) function dbo.CURRENT_PERIOD() (with an in the context constant result ) that seems to execute multiple times.

[snippet]
WHERE HO.ANVANDNING NOT IN (SELECT ANVANDNING FROM HH_EXCL_ANVANDNING)
AND HO.PERIOD_NKL=dbo.CURRENT_PERIOD(null) AND REF.PERIOD_NKL=dbo.CURRENT_PERIOD(null)
AND HO.AVTAL_STATUS=@STATUS
[end snippet]

To increase performance I extracted this function result to an input parameter and then I call the outer function from a wrapper function

[snippet]
RETURNS TABLE
AS
RETURN
(
SELECT *
from dbo.F_AVTALSLISTA2_base( @AFFARSPLAN_ID, @STATUS, dbo.CURRENT_PERIOD(null))
)
[end snippet]

where dbo.F_AVTALSLISTA2_base is the outer table-valued function.

This doesnt at all impact performance (18 secs).

BUT, if I call the outer function with a constant value, like:

[snippet]
RETURNS TABLE
AS
RETURN
(
SELECT *
from dbo.F_AVTALSLISTA2_base( @AFFARSPLAN_ID, @STATUS, 691)
)
[end snippet]

the result is only a fraction of a second away.

SQL Server seems to optimize this in a pretty obscure way.

Any hints?

/Martin

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-09-28 : 05:41:25

Can be a number of things.
Parameter sniffing, or the current_period function is badly written.
The current_period function is evaluated for every record in outer F_AVTALSLISTA2_base function!

N 56°04'39.26"
E 12°55'05.63"

aagren
Starting Member

8 Posts

Posted - 2009-09-28 : 05:53:32

quote:
Originally posted by Peso

The current_period function is evaluated for every record in outer F_AVTALSLISTA2_base function!

Yes, that seems to be the problem and was already identified as such. But it isn't the desired behaviour. When moved to an input param it shld be evaluated only once, at function call.

/Martin

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-09-28 : 05:56:15

How does this perform?

RETURNS TABLE 
AS
BEGIN
	DECLARE	@cpID INT
	
	SET	@cpID = dbo.CURRENT_PERIOD(NULL)
	
	RETURN	(
			SELECT	* 
			FROM	dbo.F_AVTALSLISTA2_base( @AFFARSPLAN_ID, @STATUS, @cpID)
		)
END

N 56°04'39.26"
E 12°55'05.63"

aagren
Starting Member

8 Posts

Posted - 2009-09-28 : 05:59:44

quote:

Originally posted by Peso

How does this perform?

RETURNS TABLE 
AS
BEGIN
	DECLARE	@cpID INT
	
	SET	@cpID = dbo.CURRENT_PERIOD(NULL)
	
	RETURN	(
			SELECT	* 
			FROM	dbo.F_AVTALSLISTA2_base( @AFFARSPLAN_ID, @STATUS, @cpID)
		)
END

N 56°04'39.26"
E 12°55'05.63"

I was under the impression I cannot declare a variable within a function.

/M

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-09-28 : 06:15:15

Well, now you have learnt something new today

N 56°04'39.26"
E 12°55'05.63"

aagren
Starting Member

8 Posts

Posted - 2009-09-28 : 06:52:46

quote:
Originally posted by Peso

Well, now you have learnt something new today

N 56°04'39.26"
E 12°55'05.63"

Or you, maybe.
The syntax with BEGIN END is not compiling at all, nor is it possible to include a variable declaration anywhere in the function body.

/Martin

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-09-28 : 07:24:21

I can spell everything out for you, if you want me to.

CREATE FUNCTION	dbo.MyVeryFirstFunction
(
	@AFFARSPLAN_ID INT, -- Or whatever datatype you have today
	@STATUS INT -- Or whatever datatype you have today
)
RETURNS @ret TABLE (Col1 inT) -- Change this to match F_AVTALSLISTA2_base function.
AS
BEGIN
	DECLARE	@cpID INT
	
	SET	@cpID = dbo.CURRENT_PERIOD(NULL)
	
	INSERT	@ret
	SELECT	* 
	FROM	dbo.F_AVTALSLISTA2_base( @AFFARSPLAN_ID, @STATUS, @cpID)

	RETURN
END

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-09-28 : 07:35:15

Or better yet, assign a variable with dbo.CURRENT_PERIOD(NULL)
before calling this new function, and use the code posted 09/28/2009 : 05:56:15

N 56°04'39.26"
E 12°55'05.63"

aagren
Starting Member

8 Posts

Posted - 2009-09-28 : 08:11:29

quote:
Originally posted by Peso

I can spell everything out for you, if you want me to.[code]CREATE FUNCTION dbo.MyVeryFirstFunction
(
@AFFARSPLAN_ID INT, -- Or whatever datatype you have today
@STATUS INT -- Or whatever datatype you have today
)
RETURNS @ret TABLE (Col1 inT) -- Change this to match

No, no - you are shifting away from an inline function.
I certainly dont want to enumerate all the columns.

/M

aagren
Starting Member

8 Posts

Posted - 2009-09-28 : 08:47:09

Well - what do you say?

We did start off with an inline function, didnt we?

And no variable declaration/initialization possible, right?

/Martin

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-09-28 : 09:03:50

The only way to keep the inline function is to resolve the dbo.CURRENT_PERIOD(NULL)
beforehand.

N 56°04'39.26"
E 12°55'05.63"

aagren
Starting Member

8 Posts

Posted - 2009-09-28 : 09:19:52

quote:
Originally posted by Peso

The only way to keep the inline function is to resolve the dbo.CURRENT_PERIOD(NULL)
beforehand.

N 56°04'39.26"
E 12°55'05.63"

And then we are back where we started - that doesnt increase performance at all since the SQL Server doesnt resolve this at function call but inlines it within the inner function.

/M

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-09-28 : 10:40:55

How about something like this? It's hard to help due to the lack of information.
It would be great if the functions and underlying tables could be explained, in order to compare execution plans.

CREATE FUNCTION	dbo.MyVeryFirstFunction
(
	@AFFARSPLAN_ID INT,	-- Or whatever datatype you have today
	@STATUS INT		-- Or whatever datatype you have today
)
RETURNS TABLE
AS
RETURN	(
		SELECT		q.* 
		FROM		(
					SELECT	dbo.CURRENT_PERIOD(NULL) AS e
				) AS d
		CROSS APPLY	dbo.F_AVTALSLISTA2_base(@AFFARSPLAN_ID, @STATUS, d.e) AS q
	)

N 56°04'39.26"
E 12°55'05.63"

aagren
Starting Member

8 Posts

Posted - 2009-09-28 : 10:46:43

quote:
Originally posted by Peso

How about something like this? It's hard to help due to the lack of information.

N 56°04'39.26"
E 12°55'05.63"

I went the long way now, reworking the function into a multi statement function, but typing the name and type of 120 columns and filling them with values isnt really what I was after.
But here it works to declare and initialize the variable and performance is very good, so happy end I guess.

Tusen tack for taking the time, Peso.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-09-28 : 10:49:15

How does the function posted 09/28/2009 : 10:40:55 perform?

N 56°04'39.26"
E 12°55'05.63"

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-09-28 : 13:25:46

Reverting back to your Entry Level DBA© ways Peter?
(I would have put the little TM, but it shows as a weird symbol instead �)

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-09-28 : 15:09:31

Yes! Need to keep in touch from my origin...

N 56°04'39.26"
E 12°55'05.63"

sanoj_av
Posting Yak Master

118 Posts

Posted - 2009-09-29 : 05:04:00

Hi Peso,
I am also facing such a similar problem. when I include a UDF in the WHERE clause of my Query it takes longer time than when I included it in my select list. I do not use any iterative operation in the Function. any idea why it happened so ?

Thanks in advance,
Sanoj

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-09-29 : 05:06:34

In a WHERE clause, the FUNCTION is evaluated for every record in the FROM part.
Start a new topic and explain your problem and maybe we can assist you.

N 56°04'39.26"
E 12°55'05.63"

sanoj_av
Posting Yak Master

118 Posts

Posted - 2009-09-29 : 05:10:07

quote:
Originally posted by Peso

In a WHERE clause, the FUNCTION is evaluated for every record in the FROM part.
Start a new topic and explain your problem and maybe we can assist you.

N 56°04'39.26"
E 12°55'05.63"

Thanks Peso,
I will post it as a new thread.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources