gpt4 book ai didi

sql-server - 在许多条件下,基于集合的计划运行速度比标量值函数慢

转载 作者:行者123 更新时间:2023-12-01 00:36:19 25 4
gpt4 key购买 nike

这个问题与其说是一个实际的代码问题,不如说是一个假设问题。但我提供了一个简化版本的代码来说明这个问题。请不要评论代码本身的愚蠢。实际代码太复杂(且专有),因此这是继续进行的最佳方式。

我有一个标量值函数如下。

CREATE FUNCTION [dbo].[Compute_value]
(
@alpha FLOAT,
@bravo FLOAT,
@charle FLOAT,
@delta FLOAT
)
RETURNS FLOAT
AS
BEGIN
IF @alpha IS NULL OR @alpha = 0 OR @delta IS NULL OR @delta = 0
RETURN 0

IF @bravo IS NULL OR @bravo <= 0
RETURN 100

IF (@charle + @delta) / @bravo <= 0
RETURN 100
DECLARE @x = DATEDIFF(GETDATE(),'1/1/2000')
RETURN @alpha * POWER((100 / @delta), (-2 * POWER(@charle * @bravo, @x/365)))
END

我听说表值函数通常比标量值函数运行得快得多,因为它们不是 RBAR。因此,我将逻辑转换为使用 #temp_table 构造只是为了对其进行基准测试。我将对 #temp_table 进行相同数量的更新,而不是大约一打 IF 语句,并且它的运行速度是标量 UDF 的两倍。

我认为这可能是因为 UDF 可以在前几个条件下快速返回,从而导致大部分标量 UDF 成为空操作,但事实并非如此。检查 #temp_table 解决方案的查询执行计划似乎表明更新导致了大部分计划成本。

我可能在这里遗漏了什么?如果我将其转换为表值函数,我是否会为每个条件语句更新整个表变量?有没有办法避免这种情况,这似乎大大减慢了速度?我在这里遗漏了什么明显的东西吗?

最佳答案

这里的关键词是INLINE TABLE VALUED FUNCTIONS。您有两种类型的 T-SQL 表值函数:多语句和内联。如果您的 T-SQL 函数以 BEGIN 语句开头,那么它将是垃圾 - 标量或其他。您无法将临时表放入内联 表值函数中,因此我假设您从标量转到多语句表值函数,这可能会更糟。

您的内联表值函数 (iTVF) 应如下所示:

CREATE FUNCTION [dbo].[Compute_value]
(
@alpha FLOAT,
@bravo FLOAT,
@charle FLOAT,
@delta FLOAT
)
RETURNS TABLE WITH SCHEMABINDING AS RETURN
SELECT newValue =
CASE WHEN @alpha IS NULL OR @alpha = 0 OR @delta IS NULL OR @delta = 0 THEN 0
WHEN @bravo IS NULL OR @bravo <= 0 THEN 100
ELSE @alpha * POWER((100 / @delta),
(-2 * POWER(@charle * @bravo, DATEDIFF(<unit of measurement>,GETDATE(),'1/1/2000')/365)))
END
GO;

请注意,在您发布的代码中,您的 DATEDIFF 语句缺少 datepart 参数。如果应该看起来像:

@x int = DATEDIFF(DAY, GETDATE(),'1/1/2000')   

更进一步 - 理解为什么 iTVF 优于 T-SQL 标量值用户定义函数很重要。这并不是因为表值函数比标量值函数快,而是因为 Microsoft 的 T-SQL 内联函数实现比非内联 T-SQL 函数的实现更快。请注意以下三个执行相同操作的函数:

-- Scalar version
CREATE FUNCTION dbo.Compute_value_scalar
(
@alpha FLOAT,
@bravo FLOAT,
@charle FLOAT,
@delta FLOAT
)
RETURNS FLOAT
AS
BEGIN
IF @alpha IS NULL OR @alpha = 0 OR @delta IS NULL OR @delta = 0
RETURN 0

IF @bravo IS NULL OR @bravo <= 0
RETURN 100

IF (@charle + @delta) / @bravo <= 0
RETURN 100
DECLARE @x int = DATEDIFF(dd, GETDATE(),'1/1/2000')
RETURN @alpha * POWER((100 / @delta), (-2 * POWER(@charle * @bravo, @x/365)))
END
GO

-- multi-statement table valued function
CREATE FUNCTION dbo.Compute_value_mtvf
(
@alpha FLOAT,
@bravo FLOAT,
@charle FLOAT,
@delta FLOAT
)
RETURNS @sometable TABLE (newValue float) AS
BEGIN
INSERT @sometable VALUES
(
CASE WHEN @alpha IS NULL OR @alpha = 0 OR @delta IS NULL OR @delta = 0 THEN 0
WHEN @bravo IS NULL OR @bravo <= 0 THEN 100
ELSE @alpha * POWER((100 / @delta),
(-2 * POWER(@charle * @bravo, DATEDIFF(DAY,GETDATE(),'1/1/2000')/365)))
END
)
RETURN;
END
GO

-- INLINE table valued function
CREATE FUNCTION dbo.Compute_value_itvf
(
@alpha FLOAT,
@bravo FLOAT,
@charle FLOAT,
@delta FLOAT
)
RETURNS TABLE WITH SCHEMABINDING AS RETURN
SELECT newValue =
CASE WHEN @alpha IS NULL OR @alpha = 0 OR @delta IS NULL OR @delta = 0 THEN 0
WHEN @bravo IS NULL OR @bravo <= 0 THEN 100
ELSE @alpha * POWER((100 / @delta),
(-2 * POWER(@charle * @bravo, DATEDIFF(DAY,GETDATE(),'1/1/2000')/365)))
END
GO

现在进行一些示例数据和性能测试:

SET NOCOUNT ON;
CREATE TABLE #someTable (alpha FLOAT, bravo FLOAT, charle FLOAT, delta FLOAT);
INSERT #someTable
SELECT TOP (100000)
abs(checksum(newid())%10)+1, abs(checksum(newid())%10)+1,
abs(checksum(newid())%10)+1, abs(checksum(newid())%10)+1
FROM sys.all_columns a, sys.all_columns b;

PRINT char(10)+char(13)+'scalar'+char(10)+char(13)+replicate('-',60);
GO
DECLARE @st datetime = getdate(), @z float;

SELECT @z = dbo.Compute_value_scalar(t.alpha, t.bravo, t.charle, t.delta)
FROM #someTable t;

PRINT DATEDIFF(ms, @st, getdate());
GO

PRINT char(10)+char(13)+'mtvf'+char(10)+char(13)+replicate('-',60);
GO
DECLARE @st datetime = getdate(), @z float;

SELECT @z = f.newValue
FROM #someTable t
CROSS APPLY dbo.Compute_value_mtvf(t.alpha, t.bravo, t.charle, t.delta) f;

PRINT DATEDIFF(ms, @st, getdate());
GO

PRINT char(10)+char(13)+'itvf'+char(10)+char(13)+replicate('-',60);
GO
DECLARE @st datetime = getdate(), @z float;

SELECT @z = f.newValue
FROM #someTable t
CROSS APPLY dbo.Compute_value_itvf(t.alpha, t.bravo, t.charle, t.delta) f;

PRINT DATEDIFF(ms, @st, getdate());
GO

结果:

scalar
------------------------------------------------------------
2786

mTVF
------------------------------------------------------------
41536

iTVF
------------------------------------------------------------
153

标量 udf 运行了 2.7 秒,mtvf 运行了 41 秒,iTVF 运行了 0.153 秒。要了解为什么让我们看一下估计的执行计划:

enter image description here

当您查看实际执行计划时,您看不到这一点,但是对于标量 udf 和 mtvf,优化器会为每一行调用一些执行不佳的子例程; iTVF 没有。引用 Paul White's career changing article about APPLY保罗写道:

You might find it useful to think of an iTVF as a view that accepts parameters. Just as for views, SQL Server expands the definition of an iTVF directly into the query plan of an enclosing query, before optimization is performed.

The effect is that SQL Server is able to apply its full range of optimizations, considering the query as a whole. It is just as if you had written the expanded query out by hand....

换句话说,iTVF 使优化器能够以在需要执行所有其他代码时无法实现的方式优化查询。 iTVF 优越的许多其他示例之一是它们是上述三种允许并行性的函数类型中唯一的一种。让我们再运行一​​次每个函数,这次启用实际执行计划并使用跟踪标志 8649(强制执行并行执行计划):

-- don't need so many rows for this test
TRUNCATE TABLE #sometable;
INSERT #someTable
SELECT TOP (10)
abs(checksum(newid())%10)+1, abs(checksum(newid())%10)+1,
abs(checksum(newid())%10)+1, abs(checksum(newid())%10)+1
FROM sys.all_columns a;

DECLARE @x float;

SELECT TOP (10) @x = dbo.Compute_value_scalar(t.alpha, t.bravo, t.charle, t.delta)
FROM #someTable t
ORDER BY dbo.Compute_value_scalar(t.alpha, t.bravo, t.charle, t.delta)
OPTION (QUERYTRACEON 8649);

SELECT TOP (10) @x = f.newValue
FROM #someTable t
CROSS APPLY dbo.Compute_value_mtvf(t.alpha, t.bravo, t.charle, t.delta) f
ORDER BY f.newValue
OPTION (QUERYTRACEON 8649);

SELECT @x = f.newValue
FROM #someTable t
CROSS APPLY dbo.Compute_value_itvf(t.alpha, t.bravo, t.charle, t.delta) f
ORDER BY f.newValue
OPTION (QUERYTRACEON 8649);

执行计划:

enter image description here

您看到的 iTVF 执行计划的箭头是并行性 - 所有 CPU(或 SQL 实例的 MAXDOP 设置允许的数量)一起工作。 T-SQL 标量和 mtvf UDF 无法做到这一点。当 Microsoft 引入内联标量 UDF 时,我会建议您使用这些 UDF,但在那之前:如果您正在寻找性能,那么内联是唯一的选择,为此,iTVF 是唯一的游戏城里。

请注意,我在谈论函数时不断强调 T-SQL...CLR 标量和表值函数可能很好,但那是另一个话题。

关于sql-server - 在许多条件下,基于集合的计划运行速度比标量值函数慢,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/50805659/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com