SQL CROSS APPLY with SQLCLR Table-Valued Function (TVF)

Question

I have a table-valued function built as a SQLCLR function calling a calculation on a remote Web Service. I was wondering if the CROSS APPLY function will call the function in parallel or in a sequential manner?

I am not sure the web service supporting the calculation is really thread-safe.

Cross-apply query:

WITH listCTE AS
(
SELECT 'definition1' AS def UNION ALL
SELECT 'definition2' AS def UNION ALL 
SELECT 'definition3' AS def 
)
SELECT calc.*
FROM listCTE a
CROSS APPLY dbo.f_webservice_calculate (a.def,'2017-06-30') calc

SQLCLR TVF function definition.

[SqlFunction(
    Name = "f_webservice_calculate",
    DataAccess = DataAccessKind.Read,
    FillRowMethodName = "fillRowMethod",           
    SystemDataAccess = SystemDataAccessKind.Read
)]
public static IEnumerable f_webservice_calculate(string def, DateTime date)
{
...calling web service using HttpWebRequest
}

score 3 · Answer 1 · answered Nov 08 '17 at 21:22

I am not sure if CROSS APPLY can do parallel or not, but if you need to make sure that it doesn't, you can force the query to remain single-threaded by adding OPTION (MAXDOP 1) to the bottom of the query.

Also, with respect to calling a web service via SQLCLR, please see the recommendations I mentioned in the following answer, especially the one about ServicePointManager and the default concurrent connection limit of just 2 that needs to be manually increased if you don't want to inadvertently introduce a much larger bottle neck into the query than you are already doing simply by calling an external resource:

SQL CLR Web Service Call: Limiting Overhead

score 3 · Answer 2 · edited Nov 13 '18 at 23:33

+1 for the question as it's an important one. This article has some useful information on the topic: Forcing a Parallel Query Execution Plan by Paul White.

Provided there aren't any parallelism inhibitors (such as a scalar UDF or system table access) the query optimizer has the option to choose a parallel or serial plan based on a number of factors.

I was wondering if the CROSS APPLY function will call the function in parallel or in a sequential manner?

APPLY (CROSS APPLY and OUTER APPLY) is a table operator just like JOIN, PIVOT and UNPIVOT. There is nothing about a table operator alone that will cause the optimizer to chose a serial or parallel plan over the other UNLESS your CROSS APPLY is referencing a multi-statement table valued function (inline table valued functions are fine).

Forcing a Serial or Parallel plan

As Solomon mentioned, you can use OPTION (MAXDOP 1) to force a serial plan. You can use the (undocumented) 8649 traceflag to force a parallel plan by adding OPTION (QUERYTRACEON 8649) at the end of your query. The will force the optimizer to choose a parallel plan provided that it can. If you use traceflag 8649 and still get a serial plan then you know that there's a parallelism inhibiting component in play.

Note that, for a documented alternative to traceflag 8649, you can use make_parallel by Adam Machanic.

Thanks for the answer. Do you think a SQLCLR function would be considered a multi-statement table valued function? My actual execution plan (without MAXDOP 1) is a Nested Loop calling the Table Valued Function which, according to the article you posted looks like a MTVF execution plan. — Vincent, Nov 09 '17 at 14:21

SQL CROSS APPLY with SQLCLR Table-Valued Function (TVF)

2 Answers2