-1

a four page long query (that creates two temporal tables, and then joins them) in sql server runs in a dozen of seconds (thanks to parallel execution). The same query packed into a user defined function (with temp tables replaced by table-variables as those are not supported in UDF) takes a dozen of minutes to compute (thanks for no-parallelism).

If I inspect the query plans, they are exactly the same, except the UDF version does not do parallel execution with SQL Server mentioning (I omit all the details as the query plans barely fit into A0 size page):

 <QueryPlan NonParallelPlanReason="CouldNotGenerateValidParallelPlan"

is there any "cheap" way to tell the sql server to try to parallelize the SQL Statements inside the UDFs?

The prototype of the function is:

CREATE  FUNCTION import (@ts_start datetime2(3), @ts_end datetime2(3)) 
RETURNS @imports TABLE (id uniqueidentifier)

while previously those two parameters where given in a declare variable:

declare 
      @ts_start datetime2(3) = '2020-06-22 00:00:00'
    , @ts_end datetime2(3)   = '2020-06-23 00:00:00'

Any way to parallelize the sqls inside a udf in sql server?

Improve this question
7
  • 1
    Instead of multi-statement TVF (RETURNS @imports TABLE (id uniqueidentifier)) why not use an inline TVF (RETURNS TABLE)?
    – Aaron Bertrand
    Jul 30, 2020 at 13:23
  • 1
    Maybe OPTION(USE HINT('ENABLE_PARALLEL_PLAN_PREFERENCE')) ? mssqltips.com/sqlservertip/4939/…
    – Jonathan Fite
    Jul 30, 2020 at 13:37
  • Hi @JonathanFite: option to give a hint is a nice one, I thought it is not available inside a function
    – arthur
    Jul 31, 2020 at 6:02
  • @AaronBertrand: good idea w.r.t. the tvf, as I compute the result only at the end of the query. Do you think this would parallelize the other sql queries inside the function?
    – arthur
    Jul 31, 2020 at 6:03
  • An inline TVF would allow the logic inside the function to be "assimilated" if you will into the outer query, and the module boundary (and the table variable) would no longer be an "excuse" for the engine to prevent parallelism - see this question, this question, and this post for some more details. I'd be surprised, though, if parallelism is the only/major factor for seconds -> minutes. Could you post the slow and fast plan?
    – Aaron Bertrand
    Jul 31, 2020 at 12:03

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy