hint OPTION(FORCE ORDER) inside user-definde funct

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)

hint OPTION(FORCE ORDER) inside user-definde funct

Author

Topic

vantive_98
Starting Member

2 Posts

Posted - 2002-11-12 : 14:51:29

How to use hint OPTION(FORCE ORDER) inside user-definde function?
I want to force optimizer use specific table order in my function.
This logic ( force table order) should be inside !!! my function,
so user of this function should use it like this
select * from my_func('a')
Problem: I have compilation error :

create table a(i int ,val char )
create table b( i int, data char)
go
create function my_func (@v char) returns table as
return ( select b.* from b JOIN a ON a.id=b.id
OPTION(FORCE ORDER)
)
go
Output:
Server: Msg 156, Level 15, State 1, Procedure my_func, Line 5
Incorrect syntax near the keyword 'OPTION'.

Any idea?

burbakei
Yak Posting Veteran

80 Posts

Posted - 2002-11-13 : 13:18:53

you can convert your inline table-valued function to multi statement
table-valued function

CREATE FUNCTION my_func (@v char)
RETURNS @ForceOrder TABLE (i int, data char)
AS
BEGIN
INSERT INTO @ForceOrder
SELECT b.i, b.data
FROM b
INNER JOIN a
ON a.i = b.i
OPTION(FORCE ORDER)
RETURN
END
GO

can you tell me what is the purpose of @v parameter of this function?

vantive_98
Starting Member

2 Posts

Posted - 2002-11-13 : 15:23:07

Thank you.
I did not use parameter in this example to simpify real query.
Question based on previous solution :
what is performance penalty for using proposed approach ?
Does it generate one more additional table ?
Thanks !

burbakei
Yak Posting Veteran

80 Posts

Posted - 2002-11-13 : 19:44:18

Unfortunately i could not find anything about comparing the performance between these two types of functions in books online or any other resource. so when i come to this situation i always create
some benchmark to compare them. you can adopt the following script to evaluate your problem in question.

SET NOCOUNT ON
GO

CREATE TABLE test (i INT)
GO

DECLARE @i INT

SET @i = 0
WHILE @i < 100000
BEGIN
INSERT INTO test VALUES (@i)
SET @i = @i + 1
END
GO

CREATE FUNCTION InLineTableFunc()
RETURNS TABLE
AS
RETURN (SELECT * FROM test)
GO

CREATE FUNCTION MultiStatementTableFunc()
RETURNS @Tbl TABLE (i INT)
AS
BEGIN
INSERT INTO @Tbl
SELECT *
FROM test

RETURN
END
GO

SET STATISTICS TIME ON
GO

SELECT *
FROM InLineTableFunc()

SELECT *
FROM MultiStatementTableFunc()

and then comapre the elapsed time for each select.
Interestingly i usually get two almost equal numbers for each elapsed time.

by the way, it is good time to tell you that using a FUNCTION TABLE
usually has a better perfomace than using temporary tables, due to locking considerations, no matter which type of FUNCTION TABLE.

if you had any other question you can mail to burbakei@yahoo.com

good lock

Edited by - burbakei on 11/13/2002 20:15:20

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources