RETURNS TABLE function performance

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

Transact-SQL (2000)

RETURNS TABLE function performance

Author

Topic

Kristen
Test

22859 Posts

Posted - 2006-10-24 : 03:35:21

I have a function that does something like:


CREATE FUNCTION dbo.MyFunction
(
	@Param1, ... several parameters ...
)
RETURNS TABLE
AS
RETURN
	SELECT
		[KEY_ID] = MyID
	FROM	dbo.MyTable
	WHERE
		    (@Param1 IS NULL OR Col1 = @Param1)
		AND ... several more ...
GO

and I use that function in code like:


SELECT TOP 500 Col1, Col2, ...
FROM	dbo.MyFunction(@Param1, ... )
	JOIN dbo.MyTable
		 ON MyID = KEY_ID

and I get this sort of performance:

Table 'MyTable'. Scan count 4,949,742, logical reads 15,713,228, physical reads 0, read-ahead reads 120,008.

OTOH if I do


DECLARE	@TableVar TABLE
(
	KEY_ID int NOT NULL,
	PRIMARY KEY
	(
		KEY_ID
	)
)

INSERT INTO @TableVar(KEY_ID)
SELECT	KEY_ID
FROM	dbo.MyFunction(@Param1, ... )

SELECT TOP 500 Col1, Col2, ...
FROM	@TableVar
	JOIN dbo.MyTable
		 ON MyID = KEY_ID

I get

Table '#05FB085B'. Scan count 0, logical reads 4, physical reads 0, read-ahead reads 0.

Table 'MyTable'. Scan count 1, logical reads 122,637, physical reads 0, read-ahead reads 72.

(which covers getting the data into the @TableVar, and then:

Table 'MyTable'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0.
Table '#05FB085B'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.

which is the difference between several minutes and about 5 seconds.

Is there some way I can get the improved performance without resorting to an @TableVar?

(The number of rows actually returned by dbo.MyFunction is 1 for the most commonly used case, but it could be more)

Kristen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-24 : 03:41:13

What happens if you do something like this in the function? Do you still get the same performance or better?

CREATE FUNCTION dbo.MyFunction
(
	@Param1, ... several parameters ...
)
RETURNS @SomeName TABLE (KeyID INT NOT NULL PRIMARY KEY)
AS

BEGIN
	INSERT @SomeName (KeyID)
	SELECT
		[KEY_ID] = MyID
	FROM	dbo.MyTable
	WHERE
		    (@Param1 IS NULL OR Col1 = @Param1)
		AND ... several more ...

	RETURN
END

And later


SELECT TOP 500 Col1, Col2, ...
FROM	dbo.MyFunction(param1, ...) x
	JOIN dbo.MyTable z
		 ON z.MyID = x.KeyID

Peter Larsson
Helsingborg, Sweden

Kristen
Test

22859 Posts

Posted - 2006-10-24 : 05:37:52

Table 'MyTable'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0.
Table '#3B4BDB3C'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.

which looks more like it! Still took 3 minutes (but the server is busy, so back-to-back comparison difficult at the moment).

However, that looks to be just the JOIN stuff in the outer query, where is the figure for the work that is actually going on inside the Function?

(MyTable has millions of rows and is going to take some sort of hit populating the @SomeName TableVar inside the Function, isn't it?)

I'm going to have to root out all my Inline Table-valued Functions and change them to Multi-statement Table-valued Functions, aren't I?

Kristen

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-10-24 : 07:41:55

probably...

EDIT:
I have no idea if this will work but what happens if
you populate the table variable inside the function and then return that?

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources