SQL CROSS应用与SQLCLR表值函数(TVF)

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?

我有一个表值函数，它作为SQLCLR函数构建，用于调用远程Web服务上的计算。我想知道交叉应用函数会以并行方式还是顺序方式调用这个函数?

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

我不确定支持计算的web服务是否真的是线程安全的。

Cross-apply query:

Cross-apply查询:

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.

SQLCLR TVF函数定义。

[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
}

2 个解决方案

#1

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.

我不确定CROSS APPLY是否可以实现并行，但是如果您需要确保它不能，您可以通过在查询底部添加选项(MAXDOP 1)来强制查询保持单线程。

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:

同时,对通过SQLCLR调用web服务,请参见建议我在下面提到的回答,尤其是关于ServicePointManager和默认的并发连接的极限2需要手动增加如果你不想无意中引入更大的瓶颈查询比你已经做简单地通过调用外部资源:

SQL CLR Web Service Call: Limiting Overhead

SQL CLR Web服务调用:限制开销。

#2

+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.

+1，因为这个问题很重要。本文有一些关于这个主题的有用信息:由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.

如果没有任何并行性的抑制剂(例如标量UDF或系统表访问)，查询优化器可以根据多个因素选择一个并行或串行计划。

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).

APPLY (CROSS APPLY和OUTER APPLY)是一个类似于JOIN、PIVOT和UNPIVOT的表操作符。除了表运算符之外，没有什么会导致优化器选择串行或并行计划，除非交叉应用引用多语句表值函数(内联表值函数很好)。

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.

正如Solomon提到的，您可以使用选项(MAXDOP 1)强制执行一个串行计划。您可以使用(未注册的)8649 traceflag在查询的末尾添加OPTION (QUERYTRACEON 8649)来强制执行并行计划。这将迫使优化器选择一个并行计划，前提是它可以这样做。如果你使用traceflag 8649并且仍然得到一个串行计划，那么你就知道在游戏中有一个并行抑制组件。

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

注意，对于traceflag 8649的一个有文档说明的替代方案，您可以使用Adam Machanic开发的make_parallel。

#1