February 11, 2009 at 10:17 am
Dear Forum,
the query optimizer is usually fairly good at generating parallel query plans, but when it comes to a table-valued function, it seems to always run sequentially.
Apparently this is because the Function needs to put its result into a Table-Variable to return it, and, as msdn says, "Queries that modify table variables do not generate parallel query execution plans. ".
My functions looks like this
CREATE FUNCTION dbo.smartfunction(@P1 sometype, @P2 sometype)
returns @tbl table (field1 sometype, field2 sometype)
as
begin
insert into @tbl
select (some complex stuff reading and filtering from existing permanent tables)
return
end
It typically reads > 100.000 Rows to return 10-20 Rows. It would benefit greatly from a parallel query plan (I tried it in a stored proc instead).
Is there any way to make a table-valued function use a parallel query plan ?
Regards
Kay
February 17, 2009 at 6:15 am
You didn't provide the actual statment/statements that are executing ("some complex stuff reading and filtering from existing permanent tables"), so I'll will give my best to help you without knowing it.
If you are executing only one select statement, however complex it is, make an inline table-valued function. That is the one that uses following sintax:
CREATE FUNCTION [ schema_name. ] function_name
...
RETURNS TABLE
RETURN select ...;
Your function would than look like this:
CREATE FUNCTION dbo.smartfunction(@P1 sometype, @P2 sometype)
returns table
return select (some complex stuff reading and filtering from existing permanent tables);
Notice that there is no declaration of @tbl table variable. This way you would circumvent the limitation of queries that use table variables and their inability to generate parallel execution plans.
On the other hand, if the code you are executing contains more than one select statement, you should insert the results into a table stored in tempdb. At the end of the function body, copy the rows from #table into @tbl table and drop the #table. This would give SQL Server an ability to use parallel execution plan.
I hope this helps...
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply