- html - 出于某种原因,IE8 对我的 Sass 文件中继承的 html5 CSS 不友好?
- JMeter 在响应断言中使用 span 标签的问题
- html - 在 :hover and :active? 上具有不同效果的 CSS 动画
- html - 相对于居中的 html 内容固定的 CSS 重复背景?
这个问题与其说是一个实际的代码问题,不如说是一个假设问题。但我提供了一个简化版本的代码来说明这个问题。请不要评论代码本身的愚蠢。实际代码太复杂(且专有),因此这是继续进行的最佳方式。
我有一个标量值函数如下。
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 秒。要了解为什么让我们看一下估计的执行计划:
当您查看实际执行计划时,您看不到这一点,但是对于标量 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);
执行计划:
您看到的 iTVF 执行计划的箭头是并行性 - 所有 CPU(或 SQL 实例的 MAXDOP
设置允许的数量)一起工作。 T-SQL 标量和 mtvf UDF 无法做到这一点。当 Microsoft 引入内联标量 UDF 时,我会建议您使用这些 UDF,但在那之前:如果您正在寻找性能,那么内联是唯一的选择,为此,iTVF 是唯一的游戏城里。
请注意,我在谈论函数时不断强调 T-SQL...CLR 标量和表值函数可能很好,但那是另一个话题。
关于sql-server - 在许多条件下,基于集合的计划运行速度比标量值函数慢,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/50805659/
C语言sscanf()函数:从字符串中读取指定格式的数据 头文件: ?
最近,我有一个关于工作预评估的问题,即使查询了每个功能的工作原理,我也不知道如何解决。这是一个伪代码。 下面是一个名为foo()的函数,该函数将被传递一个值并返回一个值。如果将以下值传递给foo函数,
CStr 函数 返回表达式,该表达式已被转换为 String 子类型的 Variant。 CStr(expression) expression 参数是任意有效的表达式。 说明 通常,可以
CSng 函数 返回表达式,该表达式已被转换为 Single 子类型的 Variant。 CSng(expression) expression 参数是任意有效的表达式。 说明 通常,可
CreateObject 函数 创建并返回对 Automation 对象的引用。 CreateObject(servername.typename [, location]) 参数 serv
Cos 函数 返回某个角的余弦值。 Cos(number) number 参数可以是任何将某个角表示为弧度的有效数值表达式。 说明 Cos 函数取某个角并返回直角三角形两边的比值。此比值是
CLng 函数 返回表达式,此表达式已被转换为 Long 子类型的 Variant。 CLng(expression) expression 参数是任意有效的表达式。 说明 通常,您可以使
CInt 函数 返回表达式,此表达式已被转换为 Integer 子类型的 Variant。 CInt(expression) expression 参数是任意有效的表达式。 说明 通常,可
Chr 函数 返回与指定的 ANSI 字符代码相对应的字符。 Chr(charcode) charcode 参数是可以标识字符的数字。 说明 从 0 到 31 的数字表示标准的不可打印的
CDbl 函数 返回表达式,此表达式已被转换为 Double 子类型的 Variant。 CDbl(expression) expression 参数是任意有效的表达式。 说明 通常,您可
CDate 函数 返回表达式,此表达式已被转换为 Date 子类型的 Variant。 CDate(date) date 参数是任意有效的日期表达式。 说明 IsDate 函数用于判断 d
CCur 函数 返回表达式,此表达式已被转换为 Currency 子类型的 Variant。 CCur(expression) expression 参数是任意有效的表达式。 说明 通常,
CByte 函数 返回表达式,此表达式已被转换为 Byte 子类型的 Variant。 CByte(expression) expression 参数是任意有效的表达式。 说明 通常,可以
CBool 函数 返回表达式,此表达式已转换为 Boolean 子类型的 Variant。 CBool(expression) expression 是任意有效的表达式。 说明 如果 ex
Atn 函数 返回数值的反正切值。 Atn(number) number 参数可以是任意有效的数值表达式。 说明 Atn 函数计算直角三角形两个边的比值 (number) 并返回对应角的弧
Asc 函数 返回与字符串的第一个字母对应的 ANSI 字符代码。 Asc(string) string 参数是任意有效的字符串表达式。如果 string 参数未包含字符,则将发生运行时错误。
Array 函数 返回包含数组的 Variant。 Array(arglist) arglist 参数是赋给包含在 Variant 中的数组元素的值的列表(用逗号分隔)。如果没有指定此参数,则
Abs 函数 返回数字的绝对值。 Abs(number) number 参数可以是任意有效的数值表达式。如果 number 包含 Null,则返回 Null;如果是未初始化变量,则返回 0。
FormatPercent 函数 返回表达式,此表达式已被格式化为尾随有 % 符号的百分比(乘以 100 )。 FormatPercent(expression[,NumDigitsAfterD
FormatNumber 函数 返回表达式,此表达式已被格式化为数值。 FormatNumber( expression [,NumDigitsAfterDecimal [,Inc
我是一名优秀的程序员,十分优秀!