SQL Server 执行速度差异很大，具体取决于向内联表函数提供参数的方式

Question

>我正在调查SQL Server中内联表函数的执行速度问题。或者这就是我认为问题所在。我遇到了

T-SQL 代码在另存为内联表值函数时速度极慢

这看起来很有希望，因为它描述了我所看到的，但我似乎遇到了相反的问题 - 当我将变量传递给我的函数时，它花了 17 秒，但是当我在查询窗口中运行我的函数的代码时，使用 DECLARE 语句为变量(我认为有效地使它们成为文字(，它在毫秒内运行。相同的代码，相同的参数 - 只是将它们包装在一个内联表函数中似乎将其拖了下来。

我试图将查询减少到仍然表现出该行为的最小代码。我正在使用许多现有的内联表函数(所有这些函数多年来都运行良好(，并设法将我的代码简化为只需要调用一个现有的内联表函数即可突出显示速度差异。但是在这样做的过程中，我注意到了一些非常奇怪的事情

SELECT strStudentNumber
FROM dbo.udfNominalSnapshot('2019', 'REG')

需要 17 秒，而

DECLARE @strAcademicSessionStart varchar(4) = '2019'
DECLARE  @strProgressCode varchar(12)= 'REG'
SELECT strStudentNumber
FROM dbo.udfNominalSnapshot(@strAcademicSessionStart, @strProgressCode)

需要毫秒！因此，与将代码包装在内联表函数中无关，而与如何将参数传递给其中的嵌套函数有关。根据引用的文章，我猜有两种不同的执行计划在起作用，但我不知道为什么/如何，更重要的是，我能做些什么来说服SQL Server使用高效的执行计划？

附言这是响应注释请求的内部UDF调用的代码

ALTER FUNCTION [dbo].[udfNominalSnapshot] 
(   
@strAcademicSessionStart varchar(4)='%',
@strProgressCode varchar(10)='%'
)
RETURNS TABLE 
AS
RETURN 
(
SELECT     TOP 100 PERCENT S.strStudentNumber, S.strSurname, S.strForenames, S.strTitle, S.strPreviousSurname, S.dtmDoB, S.strGender, S.strMaritalStatus, 
S.strResidencyCode, S.strNationalityCode, S.strHESAnumber, S.strSLCnumber, S.strPreviousSchoolName, S.strPreviousSchoolCode, 
S.strPreviousSchoolType, 
COLLEGE_EMAIL.strEmailAddress AS strEmailAlias, 
PERSONAL_EMAIL.strEmailAddress AS strPersonalEmail,
P.[str(Sub)Plan], P.intYearOfCourse, P.strProgressCode, 
P.strAcademicSessionStart, strC2Knumber AS C2K_ID, AcadPlan,  strC2KmailAlias
,ISNULL([strC2KmailAlias], [strC2Knumber]) + '@c2kni.net' AS strC2KmailAddress
FROM         dbo.tblStudents AS S
LEFT JOIN
dbo.udfMostRecentEmail('COLLEGE') AS COLLEGE_EMAIL ON S.strStudentNumber = COLLEGE_EMAIL.strStudentNumber
LEFT JOIN
dbo.udfMostRecentEmail('PERSONAL') AS PERSONAL_EMAIL ON S.strStudentNumber = PERSONAL_EMAIL.strStudentNumber
INNER JOIN
dbo.udfProgressHistory(@strAcademicSessionStart) AS P ON S.strStudentNumber = P.strStudentNumber
WHERE     (P.strProgressCode LIKE @strProgressCode OR (SUBSTRING(@strProgressCode, 1, 1) = '^' AND P.strProgressCode NOT LIKE SUBSTRING(@strProgressCode, 2, LEN(@strProgressCode)))) AND 
(P.strStudentNumber NOT IN
(SELECT     strStudentNumber
FROM          dbo.tblPilgrims
WHERE      (strAcademicSessionStart = @strAcademicSessionStart) AND (strScheme = 'BEI')))
ORDER BY P.[str(Sub)Plan], P.intYearOfCourse, S.strSurname
)

Answer 1

扩展@Ross Pressers 评论，这可能不是一个真正的答案，但展示了正在发生的事情(一点(，以及我对正在发生的事情的理解(这可能是错误的！

最后运行安装代码，然后....

在查询计划打开的情况下执行以下命令 (Ctrl-M(...(注意：根据随机数生成器，您可能会或可能不会得到任何结果，这不会影响计划(

declare @one varchar(100) = '379', @two varchar(200) = '726'
select * from wibble(@one, @two)  -- 1 
select * from wibble('379', '726') -- 2
select * from wibble(@one, @two) OPTION (RECOMPILE) -- 3
select * from wibble(@one, @two)  -- 4

警告。以下是我的系统上发生的情况，您的里程可能会有所不同...

--1(和 -- 4(是最贵的。

SQL Server 创建一个通用计划，因为它不知道参数是什么(是的，它们已定义，但该计划适用于 wibble(@one、@two(，此时参数值为"未知"( https://www.brentozar.com/pastetheplan/?id=rJtIRwx_r

-- 2 有不同的计划

在这里，sql server知道参数是什么，所以可以创建一个特定的计划，这与--1完全不同 https://www.brentozar.com/pastetheplan/?id=rJa9APldS

-- 3 与 --2 具有相同的计划

进一步测试，添加 OPTION(重新编译(使 SQL Server 为特定执行 wibble(@one， @two( 创建特定计划，因此我们得到与 --2 相同的计划

--4 是为了完整起见，以表明毕竟关于通用计划的混乱仍然存在

因此，在这个简单的示例中，我们调用了一个参数化的 TVF，这些值作为参数或内联传递，根据 OP 生成不同的执行计划和不同的执行时间

建立

use tempdb
GO
drop table if EXISTS Orders 
GO

create table Orders (
OrderID int primary key,
UserName varchar(50),
PhoneNumber1 varchar(50),
)
-- generate 300000 with randon "phone" numbers
;WITH TallyTable AS (
SELECT TOP 300000 ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS [N]
FROM dbo.syscolumns tb1,dbo.syscolumns tb2 
)
insert into Orders
select n, 'user' + cast(n as varchar(10)), cast(CRYPT_GEN_RANDOM(3) as int)
FROM TallyTable;

GO
drop function if exists wibble
GO
create or alter function wibble (
@one varchar(4) = '%'
, @two varchar(4) = '%'
)
returns table
as
return select * from Orders
where PhoneNumber1 like '%' + @one + '%'
and PhoneNumber1 like '%' + @two + '%'
or (SUBSTRING(@one, 1, 1) = '^' AND PhoneNumber1 NOT LIKE SUBSTRING(@two, 2, LEN(@two)))
and (select 1) = 1
GO

Answer 2

通过跟进Ross Presser对udfProgressHistory复杂性的观察，克服了这个问题(我不会说"修复"(。这从连接到自身的表tblProgressHistory中吸取数据。该表每年添加一次。我认为今年额外的 2K 记录一定导致了使用特定执行计划时成本的突然上涨。我删除了>2K冗余记录，我们又回到了亚秒级执行。