sql server表值函数与内联sql语句

ulydmbyx 于 2021-07-27 发布在 Java

关注(0)|答案(2)|浏览(302)

我认为函数总是比存储过程中的内联查询慢。这是因为函数不是预编译的，所以编译函数的额外开销减少了。
但以下证明是错误的。
我创建了一个多行表值函数

CREATE FUNCTION [dbo].[UDF_SplitString] 
(
    @TheString NVARCHAR(255), 
    @Delimiter CHAR(1) = ' '
)
RETURNS @Ret TABLE (Id INT IDENTITY(1, 1) PRIMARY KEY, Value NVARCHAR(255))
AS
BEGIN
    DECLARE @X XML = (SELECT CONVERT(XML, ' <root> <s>' + REPLACE(@TheString, @Delimiter, '</s> <s>') + '</s>   </root> '))

    INSERT INTO @Ret (Value)
    SELECT [Value] = T.c.value('.', 'NVARCHAR(20)')
    FROM @X.nodes('/root/s') T(c)   
    RETURN 
END

然后，创建了一个存储过程，该过程调用了此函数，以及此函数中的相同sql语句

CREATE PROCEDURE Test
AS
BEGIN
    DECLARE @Split CHAR(1) = ' '
    DECLARE @X XML
    DECLARE @TestString VARCHAR(255) = 'Contrary to popular belief, Lorem Ipsum is not simply random text'

    -- using a TVF
    SELECT Value FROM dbo.UDF_SplitString(@TestString, @Split)

    -- without TVF
    SELECT @X = CONVERT(XML, ' <root> <s>' + REPLACE(@TestString, @Split, '</s> <s>') + '</s>   </root> ')
    SELECT [Value] = T.c.value('.', 'NVARCHAR(20)') FROM @X.nodes('/root/s') T(c)
END

现在当我看到 Estimated Execution Plan ，它显示50%-50%（这是我所期望的）。
但当我看到 Actual Execution Plan ，表值函数显然是赢家。我试着给每个查询计时，结果也证实了这一点。我还改变了查询的顺序，尝试使用更短和更长的字符串，但结果仍然相同。

有人知道这是怎么回事吗？tvf如何优于等效的内联查询？

sql sql-server tsql

来源：https://stackoverflow.com/questions/63158294/table-valued-functions-vs-inline-sql-statements

2条答案

按热度按时间

zf9nrax11#

你真的认为0%和100%是你期望的“由于预编译而运行得更快”的数字吗？
从您包含的屏幕中，可以看到更多关于mssql成本计算的限制。tvf调用中的运行时完全从分析中跳过，因此您实际上丢失了信息。
为了公平地进行实验，我建议您可以通过记录过程调用的开始和结束时间来计时执行，使用两个单独的过程，一个调用tvf 1000次，一个调用非tvf 1000次。这会给你更多的实际执行时间。
但回到问题上来，根据我的理解，包括函数或过程内部的静态查询在预编译方面应该有类似的结果。所以我和你有同样的50%-50%的期望。

赞(0）回复(0）举报 2021-07-27

g0czyy6m2#

--ctrl + M / actual execution plan
----------------------------------

CREATE FUNCTION [dbo].[UDF_SplitString_x] 
(
    @TheString NVARCHAR(MAX), 
    @Delimiter CHAR(1) = ' '
)
RETURNS @Ret TABLE (Id INT IDENTITY(1, 1) PRIMARY KEY, Value NVARCHAR(255))
AS
BEGIN
    DECLARE @X XML = (SELECT CONVERT(XML, ' <root> <s>' + REPLACE(@TheString, @Delimiter, '</s> <s>') + '</s>   </root> '))

    INSERT INTO @Ret (Value)
    SELECT [Value] = T.c.value('.', 'NVARCHAR(255)')
    FROM @X.nodes('/root/s') T(c)   
    RETURN 
END
GO

CREATE FUNCTION [dbo].[UDF_SplitString_y] 
(
    @TheString NVARCHAR(MAX), 
    @Delimiter CHAR(1) = ' '
)
RETURNS @Ret TABLE (Id INT IDENTITY(1, 1) PRIMARY KEY, Value NVARCHAR(255))
AS
BEGIN
    DECLARE @X XML = (SELECT CONVERT(XML, ' <!--' + REPLACE(@TheString, @Delimiter, '--> <!--') + '-->'))

    INSERT INTO @Ret (Value)
    SELECT [Value] = T.c.value('.', 'NVARCHAR(255)')
    FROM @X.nodes('comment()') T(c)   
    RETURN 
END 
GO

DECLARE @Split CHAR(1) = ' '
DECLARE @TestString nvarchar(max) = replicate(cast('Contrary to popular belief, Lorem Ipsum is not simply random text' as nvarchar(max)), 230)

declare @d datetime2;

select @d = sysdatetime()    
SELECT * FROM [dbo].[UDF_SplitString_y](@TestString, @Split)  
select datediff(ms, @d, sysdatetime()) split_y_ms;

select @d = sysdatetime()  
SELECT * FROM dbo.UDF_SplitString_x(@TestString, @Split) --same cost...
select datediff(ms, @d, sysdatetime()) as split_x_ms; -- diff execution time
GO

--caveat
SELECT * FROM dbo.UDF_SplitString_x(N'this is & a test' , ' ') --error, xml invalid chars
go
SELECT * FROM dbo.UDF_SplitString_y(N'this is & a test' , ' ') --ok
go
SELECT * FROM dbo.UDF_SplitString_y(N'this is <!-- a test' , ' ') --error, comment markup in the string
go

drop function [dbo].[UDF_SplitString_x] 
go
drop function [dbo].[UDF_SplitString_y] 
go

赞(0）回复(0）举报 2021-07-27

我来回答

sql server表值函数与内联sql语句

2条答案

相关问题

热门标签

最新问答