SQL Server多语句UDF-临时需要的数据存储方式

Question

我有一个相对复杂的查询，有几个自连接，适用于相当大的表。 为了使该查询执行得更快，我只需要使用数据的子集。 取决于传递的参数，所述数据子集的范围可以在12000行和120000行之间。

更多详细信息可以在这里找到：自连接中引用的 SQL Server CTE 速度慢< /a>

如您所见，我之前使用 CTE 返回数据子集，这导致了一些性能问题，因为 SQL Server 为每个连接重新运行 CTE 中的 Select 语句，而不是简单地运行一次并重用其数据集。

另一种方法是使用临时表，工作速度要快得多（同时在 UDF 主体之外的单独窗口中测试查询）。 然而，当我尝试在多语句 UDF 中实现这一点时，SQL Server 严厉地提醒我，出于某种原因，多语句 UDF 不支持临时表...

但是 UDF 确实允许表变量，所以我尝试了这一点，但性能绝对糟糕，因为我的查询需要 1 分 40 秒才能完成，而 CTE 版本仅需要 40 秒。 我相信表变量的速度很慢，原因在此线程中列出： 表变量在 SQL Server 存储过程中插入时性能较差

临时表版本大约需要 1 秒，但由于 SQL Server 限制，我无法将其变成函数，并且我有 将表返回给调用者。

考虑到 CTE 和表变量都太慢，并且临时表在 UDF 中被拒绝，为了让我的 UDF 快速执行，我有什么选择？

预先非常感谢。

Answer 1

在许多这样的情况下，我们需要做的就是为这些表变量声明主键，而且速度又很快。

Answer 2

设置和使用 进程键控表，请参阅文章：来自如何在存储过程之间共享数据作者：厄兰·索马斯科格

Answer 3

我使用的一种笨拙的解决方法涉及如下代码（伪代码如下）：

CREATE TEMP TABLE #foo

EXECUTE MyStoredProcedure

SELECT *
 from #foo

GO

--  Stored procedure definition
CREATE PROCEDURE MyStoredProcedure
AS

INSERT #foo values (whatever)

RETURN
GO

简而言之，存储过程引用并使用由调用过程（或例程）创建的临时表。这会起作用，但如果您没有清楚地记录下来，其他人可能会感到困惑，无法了解正在发生的事情，并且您将得到重新编译、统计数据重新计算和其他奇怪的情况，可能消耗不需要的时钟周期。